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

enniweswenniwesw Community Edition User

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 Vertica Employee Administrator
    edited July 2021 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

  • enniweswenniwesw Community Edition User

  • SergeBSergeB - Select Field - Employee
    edited July 2021

    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;

  • enniweswenniwesw Community Edition User

    @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