Need help in using Regexp

March 11, 2022

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.

>Solution :

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.