Search code examples
sqloracle-databaseregexp-replaceregexp-substr

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.

Solution

  • 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