need SQL select REGEXP that extracts the string of numbers between string <run_id> and </run_id>

enniwesw

need SQL select REGEXP that extracts the string of numbers between string and

field_name
37608897
12906044
21163375
8164799
11828737
20941947
1846072
32051061
31531882
37497724

The result should be.

field_name
37608897
12906044
21163375
8164799
11828737
20941947
1846072
32051061
31531882
37497724

SruthiA

@enniwesw Please find the solution below

dbadmin=> select substring (text, instr(text, '')+length('') , instr(text, '')-(instr(text,'')+length(''))) from test_str;

substring

1546776
15423476568
3476568
3476
(4 rows)

dbadmin=>

enniwesw

SergeB

In your example, the following might be sufficient as there is only one number to grep.

select regexp_substr(text,'\d+') from test_str;

Of if you wanted to grep the numbers between the run_id tags

select regexp_substr(text,'<run_id>(\d+)</run_id>',1,1,'',1) from ztest_str;

enniwesw

@SergeB , many thanks, the query greping the numbers between the run_id tags works magic!

select regexp_substr(text,'(\d+)',1,1,'',1) from ztest_str;