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

Best Answer

  • SruthiASruthiA Employee
    edited July 21 Accepted Answer

    @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=>

Answers

  • SergeBSergeB Employee
    edited July 22

    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;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.