Jimnotgym Jimnotgym - 3 months ago 10
SQL Question

adding a pipe seperator between characters with Oracle SQL

I have a string like 'foobar' which i need to like be 'f | o | o | b | a | r' in a query (Oracle 12 SQL). That is " | " between each letter but not at the end. The length of the string varies and may be null.

more examples:

'foo'> 'f | o | o'

NULL> ''

I have tried using regexp_replace

select regexp_replace(foobar,'.',' | ') from dual


and lots of other combinations. I have found examples for regex that don't seem to work in Oracle, like (?<=.)(?!$).

Any help very gratefully received

Answer

regexp_replace

will do the work:

select regexp_replace('foobar','(.)','\1 | ') from dual

will give you

f | o | o | b | a | r | 

A simple way to get rid of the last bar (edit, missing the blanks):

select substr(regexp_replace('foobar','(.)','\1 | '),1, length('foobar')*4-3) from dual

output

f | o | o | b | a | r

Proof of concept (just to satisfy Raj_te I really don't see what's wrong)

with t as 
(
select 1 as id, 'foobar' as field1 from dual union all
select 2 as id, 'fooobar' as field1 from dual union all
select 3 as id, 'foooobar' as field1 from dual union all
select 4 as id, 'fooooobar' as field1 from dual union all
select 5 as id, 'foooooobar' as field1 from dual union all
select 6 as id, 'fooooooobar' as field1 from dual union all
select 7 as id, 'foooooooobar' as field1 from dual )
select substr(regexp_replace(field1,'(.)','\1 | '),1, length(field1)*4-3) 
 from t
 order by id;

OUTPUT

f | o | o | b | a | r
f | o | o | o | b | a | r
f | o | o | o | o | b | a | r
f | o | o | o | o | o | b | a | r
f | o | o | o | o | o | o | b | a | r
f | o | o | o | o | o | o | o | b | a | r
f | o | o | o | o | o | o | o | o | b | a | r