sqlregexdb2

Regex modification in SQL


when I try out this SQL in a db2 environment, it gives me correctly the result I want, the digits of the string

select 
CAST
(
  REGEXP_REPLACE(Word, '[^0-9]', '') AS INTEGER
) AS NUMBER_PART
from
(
  values
  (1, 'this, 1234, that')
, (2, 'this 6753')
, (3, 'this 8765 that')
, (4, 'that 98565')
, (5, '123 this')
) t(id, word)

I would like to exptend this, however, so that a sith row can be used:

(6, '404-354156,390575454,Wertpapierdepot,8')

currently, this would give an overflow error, as there are several digits in this sixth. I would like it to ignore all numbers with a hyphen. So "X-XX" numbers should be omitted. Plus I would like all one digit numbers to be ignores, so that I also get 390575454 when I add the sith row. Is that possible with one SQL?


Solution

  • DB2

    SELECT 
        CAST(
            REGEXP_SUBSTR(
                REGEXP_REPLACE(Word, '(?<![0-9])[0-9](?![0-9])', ''), -- Remove all standalone single digits (0-9)
                '[0-9]+'
            ) AS INTEGER
        ) AS NUMBER_PART,
        TRIM(REGEXP_REPLACE(REGEXP_REPLACE(Word, '\\b[0-9]\\b', ''), '[0-9]', '')) AS TEXT_PART -- Remove digits to get text part
    FROM
        (
            VALUES
                (1, 'this, 1234, that'),                -- Expect: 1234
                (2, 'this 6753'),                       -- Expect: 6753
                (3, 'this 8765 that'),                  -- Expect: 8765
                (4, 'that 98565'),                      -- Expect: 98565
                (5, '123 this'),                        -- Expect: 123
                (6, '390575454,Wertpapierdepot,8'),    -- Expect: 390575454
                (7, '2,390575454,Wertpapierdepot,4,1'), -- Expect: 390575454
                (8, 'just 8 and some text 123'),        -- Expect: 123 
                (9, 'numbers like 3, 4, and 5')         -- Expect: NULL (no digits left)
        ) AS t(id, Word);