Need help in using Regexp

Question

SELECT value from table1 limit 3;

When I run the above query, I'm getting the below results:

;12770258;;;;103=::dupe::0|112=::dupe::0|114=search-results#product-id#Lettuce#not-recommended#r02#s01
;12880258;;;;103=::dupe::0|112=::dupe::0|114=search-results#product-id#Lettuce#not-recommended#r02#s01
;12990258;;;;103=::dupe::0|112=::dupe::0|114=search-results#product-id#Lettuce#not-recommended#r02#s01

I want the results to be displayed as below:

12770258
12880258
12990258

How to implement this using Regular Expression? Kindly guide.

Answer 1

We can use REGEXP_SUBSTR here:

SELECT REGEXP_SUBSTR(value, '\\d+', 1, 1, 'i', 1) AS output
FROM yourTable;

The above call to REGEXP_SUBSTR finds the first digit in the column, starting the search at the start of the column. In regex, \d+ matches a group of one or more digit characters.

Answer 2

you could just use SPLIT if you know it's always the "second" value in the series. If that is not true, then the REGEPX version will not help you ether.

SELECT ';12770258;;;;103=::dupe::0|112=::dupe::0|114=search-results#product-id#Lettuce#not-recommended#r02#s01' as col1
    ,split(col1,';')[1]::text;

COL1	SPLIT(COL1,';')[1]::TEXT
;12770258;;;;103=::dupe::0|112=::dupe::0|114=search-results#product-id#Lettuce#not-recommended#r02#s01	12770258