[SQL] 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?

Continue reading...