Anastasia  Sisordia Anastasia Sisordia - 1 month ago 6
SQL Question

Oracle regexp without white space

I need to replace all places without white space beatween 2 words, where first of them terminated with point, with this two words with white space beatween them.

For example, I have string like

'num.some'
and I need
'num. some'


But if I have
'num. some'
, I don't need
'num. some'
(<-this have 2 whitespace)

And if I have
'123.4'
, I also don't want
'123. 4'

And if I have
'123.some'
, I need
'123. some'


I tried different combinations of regexp, but I always had something wrong with my answers.

Answer

Something like this might help you:

WITH examples AS (
  SELECT 'num.some' str FROM dual
  UNION 
  SELECT 'num. some' str FROM dual
  UNION 
  SELECT '123.4' str FROM dual
  UNION 
  SELECT '123.some' str FROM dual
)
SELECT str, REGEXP_REPLACE(str,'([a-zA-Z0-9]+)\.([a-zA-Z]+)','\1. \2') replaced
FROM examples

This looks for a point after a letter followed by a letter without blank space