i have a table with a 250 char column, with a content like:
AA1BB1CC1DD1
AA1DD1EE1
AA1XX1EE2FF1AB2CB2
it's a string used like an array of 2char + 1 char. I need to insert 2 spaces after the first 2 of each array's element:
AA 1BB 1CC 1DD 1
AA 1DD 1EE 1
AA 1XX 1EE 2FF 1AB 2CB 2
is there a system function (maybe regexp_replace?) or i need write a program?
You can do it with REGEXP_REPLACE (tested with DB2 for IBM i 7.3)
with table1 (c1) as (
values
'AA1BB1CC1DD1',
'AA1DD1EE1',
'AA1XX1EE2FF1AB2CB2',
'AA1A51B11'
)
select c1, regexp_replace(c1, '(\w{2})(\d)', '\1 \2') modified
from table1
C1 | MODIFIED |
---|---|
AA1BB1CC1DD1 | AA 1BB 1CC 1DD 1 |
AA1DD1EE1 | AA 1DD 1EE 1 |
AA1XX1EE2FF1AB2CB2 | AA 1XX 1EE 2FF 1AB 2CB 2 |
AA1A51B11 | AA 1A5 1B1 1 |