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.

Tim Biegeleisen · Accepted Answer · 2022-03-11 08:53:45Z

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.

Simeon Pilgrim · Accepted Answer · 2022-03-11 08:46:58Z

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

Collectives™ on Stack Overflow

Need help in using Regexp

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related