How to extract numbers from a string field in a table?

General
Enthusiast

How to extract numbers from a string field in a table?

Hi, I have a field1 in my table1 that has letters and numbers: ABY-1234567893 (3 letters,than - than 10 digits)


i like to join that field to a field2 in my table 2. the issue is that field2 in my table 2 doesn't have letters: it's populated as numbers only :1234567893

 

my question is how to get rid of letters and dash in my field 1 so i can be able to join it on field 2 in my table 2 and would I be able to join it on field 2 after getting rid of letters?

 

Thanks,

Mike

1 REPLY
Junior Contributor

Re: How to extract numbers from a string field in a table?

If it's always the same pattern you can simply use substring:

substring(field2 from 5) -- skip the first 4 characters

If the number of characters before the digits vary, but there's always a dash, you can split the string in two and return the 2nd token:

StrTok(field2, '-', 2)

Otherwise use a RegEx to extract a series of digits:

RegExp_Substr(field2, '\d+')