CODEWITHSUNDEEP
oracle-database
Scott
Scott

Reputation: 49

To add and remove space in a string using sql functions for a sql query

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?

Upvotes: 1

Views: 1725

Answers (4)

Aramillo
Aramillo

Reputation: 3216

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

Upvotes: 0

Gary_W
Gary_W

Reputation: 10360

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.

Upvotes: 0

przemo_pl
przemo_pl

Reputation: 87

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;

Upvotes: 0

Rene
Rene

Reputation: 10541

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,' .','.'),'. ','.'),'.','. ')

Upvotes: 1

Related Questions