Oracle regexp without white space

Question

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.

Please post what you tried so far and the problems you are having — Aleksej, Oct 27 '16 at 09:21
Could you have `some.123` in the data, and does that need to have a space added also? — , Oct 27 '16 at 10:57

score 2 · Accepted Answer · answered Oct 27 '16 at 09:45

2

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

answered Oct 27 '16 at 09:45

pablomatico

2,222
20
25

it`s duplicate two times second word (word after .) I had 'dol.USA' and this do 'dol.USA. USA' I dont understand why? – Oct 27 '16 at 12:18
@AnastasiaSisordia it's working for me with 'dol.USA' too – pablomatico Oct 28 '16 at 09:11

score 0 · Answer 2 · answered Oct 27 '16 at 10:43

This looks for all combinations (letter.letter, letter.digit, digit.letter) and adds a space after the . while leaving digit.digit unchanged.

with
     inputs ( str ) as (
       select 'ab.sw'  from dual union all
       select 'ab. sw' from dual union all
       select '12.33'  from dual union all
       select '12. 33' from dual union all
       select 'ab.123' from dual union all
       select '1. ab'  from dual union all
       select '1.abc'  from dual
     )
-- END test data. Solution (SQL query) begins below this line.
select str, regexp_replace(str, 
          '(([[:alpha:]]\.)([[:alpha:]])|([[:alpha:]]\.)(\d)|(\d\.)([[:alpha:]]))',
          '\2\4\6 \3\5\7') as new_str
from   inputs
;


STR     NEW_STR
------  -------
ab.sw   ab. sw
ab. sw  ab. sw
12.33   12.33
12. 33  12. 33
ab.123  ab. 123
1. ab   1. ab
1.abc   1. abc

score 0 · Answer 3 · answered Oct 27 '16 at 11:59

Maybe you don't need a regexp. Plain replace() might serve you well.

(test data with regards to @mathguy)

with
     inputs ( str ) as (
       select 'ab.sw'  from dual union all
       select 'ab. sw' from dual union all
       select '12.33'  from dual union all
       select '12. 33' from dual union all
       select 'ab.123' from dual union all
       select '1. ab'  from dual union all
       select '1.abc'  from dual
     )
-- END test data. Solution (SQL query) begins below this line.
select replace(
  replace(str, '.', '. ') -- replace all dots by dot+space
, '  '
, ' '
) -- replace all double spaces by a single space
from   inputs
;

Oracle regexp without white space

3 Answers3