Extract Just Numbers from String of Characters

[jane]

Good afternoon,

Can someone please help me figure out a way that I can extract just the numbers from a text field?

So if I have data like this:

AAAAA92112BB
ADFSA92113BB
AAxAA921V
98122CVAAAA

I would like to get these values in a query result set:

92112
92113
921
98122

Thanks in advance for any help!

OfferID
---------
19584

Tags: codesnip, Regex, SQL, Tip, Vertica

[id10t]

Hi!

Code: Select all

daniel=> select text, regexp_substr(text, '\d+') from jane ;
     text     | regexp_substr 
--------------+---------------
 98122CVAAAA  | 98122
 AAAAA92112BB | 92112
 AAxAA921V    | 921
 ADFSA92113BB | 92113
(4 rows)

[jane]

Hi! I found that same solution on another site but I couldn't get it to run. I kept getting a weird message and just a list of the tables in my database:

Code: Select all

dbadmin=> SELECT regexp_substr(v_data1, `\d+`) FROM vd;
unterminated quoted string
                            List of tables
    Schema    |            Name            | Kind  |  Owner  | Comment 
--------------+----------------------------+-------+---------+---------
 viololet1    | abstract_dim               | table | dbadmin | 
 viololet1    | admit_dim                  | table | dbadmin | 
 viololet1    | arrangement_dim            | table | dbadmin | 

etc...

But your solution worked. But I don't know why?

[id10t]

Hi!

Most of blogs like blogger/wp swaps APOSTROPHE to GRAVE ACCENT, so you copied from blog with accent and from me with apostrophe (and should be apostrophe).

[jane]

Yes, that was it. Thanks a lot for your assistance!