I've got a requirement.There is a name column/field and it contains name like 'A . aaron' , I want to remove the space before the dot and add space after the occurrence of dot. And I also want to leave if there is space already present after the dot. I tried with replace to remove the space first and after that I don't know how to add space after the occurrence of the dot. Can anyone help me with that?
4
-
can you show some sample data, expected output and also the code you have tried so far, thanksdavegreen100– davegreen1002015-08-06 08:03:57 +00:00Commented Aug 6, 2015 at 8:03
-
Select replace('A . aaron',' ','') from dual;Scott– Scott2015-08-06 08:06:42 +00:00Commented Aug 6, 2015 at 8:06
-
I used that to remove the eror and output looked like this: A.aaron.But my expected output has to look like A. aaronScott– Scott2015-08-06 08:07:11 +00:00Commented Aug 6, 2015 at 8:07
-
In the future, please include sample "before" data showing all possible conditions, and the "after" data, the desired output given all of those conditions.Gary_W– Gary_W2015-08-06 14:55:53 +00:00Commented Aug 6, 2015 at 14:55
Add a comment
|
4 Answers
1 Use replace to remove space before dot.
replace(column,' .','.')
2 Use Replace to remove space after dot.
replace(column,'. ','.')
3 Add space after dot
replace(column,'.','. ')
This adds up to:
replace(replace(replace(column,' .','.'),'. ','.'),'.','. ')
Comments
Isn't that cleaner? I'm not sure what is desired input data. Does it always have a space-dot-space substring or any of those are optional? You can always use regexp_replace in that case
select replace('A . aaron',' . ','. ') from dual;
Comments
It can be done in one regexp_replace call. The logic is to group around all of the components of the string, then put the groups together in the order you want. Try all scenarios that the data could be in to test.
SQL> with tbl(name) as (
2 select 'A . aaron' from dual
3 union
4 select 'A. aaron' from dual
5 union
6 select 'A .aaron' from dual
7 union
8 select 'A.aaron' from dual
9 )
10 select name, regexp_replace(name, '^(\w)( )?(\.)( )?(.*)', '\1\3 \5') fixed_name
11 from tbl;
NAME FIXED_NAME
--------- ---------------
A . aaron A. aaron
A .aaron A. aaron
A. aaron A. aaron
A.aaron A. aaron
SQL>
The match pattern explained:
^ Match the beginning of the string
( Start first remembered group
\w Match a word. matches up to a space or punctuation character
) End first remembered group
( )? Followed by the second group which is an optional space
(\.) Followed by a literal period (third group)
( )? Followed by the 4th group which is an optional space
(.*) Followed by the 5th remembered group, the rest of the string.
Replace pattern explained:
\1 Replace with the first remembered group
\3 Followed by the 3rd remembered group which should be the literal period
<space> Followed by a space
\5 Followed by the rest of the string
EDIT: Different grouping/replace technique which captures and ignores one or more space or period characters after the initial word and before the rest of the string.
select name, regexp_replace(name, '^(\w)([ \.]+)(.*)', '\1. \3') fixed_name
from tbl;
It's interesting to note that the period in the match regex needs to be escaped (otherwise it's a special regex symbol meaning any character), where in the replace string, it doesn't as it's a literal period there.
Comments
You can also use this way:
select regexp_replace(replace('A . aaron.xsd',' .','.'),'\.(\s)?','. ') from dual;
Or this one(this removes extra spaces before and after dot):
select regexp_replace('A . aaron -- A. aaron -- A.aaron','(\s)*\.(\s)*','. ')
from dual
Comments
