Search code examples
sqlstringdb2-400

How do I insert 2 spaces every 2 characters in a string?

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?

Solution

  • 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

    fiddle