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

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

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;

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.

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