Anastasia  Sisordia Anastasia Sisordia - 1 year ago 144
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

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
, I also don't want
'123. 4'

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

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

Answer Source

Something like this might help you:

WITH examples AS (
  SELECT 'num.some' str FROM dual
  SELECT 'num. some' str FROM dual
  SELECT '123.4' str FROM dual
  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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download