Need help in using Regexp

user_16395449 0
Viewed 25
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.

snowflake-schema snowflake-cloud-data-platform
2 Answers

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
user_439920

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.

user_18632290
Related