The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
need SQL select REGEXP that extracts the string of numbers between string <run_id> and </run_id>

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
0
Answers
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;
@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;