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?
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);