0

Have a large table with varchar(100) - OrderNum field values like so:

"BO2003056-2"

Need to extract all numeric characters from this field for subsequent joins to another table.

Ex: "BO2003056-2" would transform to "20030562"

Since RegEx does not exist natively in TSQL, what is the fastest way to do this in TSQL?

SQL 2019 (db compat mode 2014)

1

1 Answer 1

5

Something like this, may be:

with t (input) as (select 'BO2003056-2')
select 
 replace(
  translate(
   input,
   'ABCDEFGHIJKLMNOPQRSTUVXYZ-',
   '                          '
  ),
  ' ', ''
 ) 
from t

You'll have to test for yourself whether this is "the fastest", but it's probably the simplest method.

Note that in the SQL Server version of TRANSLATE() the third argument (the string of replacement characters) must be of the same length as the second argument (the characters to be replaced).

You can avoid having to count all the characters you want to remove:

with t (input, unwanted) as (
  select 'BO2003056-2', 'ABCDEFGHIJKLMNOPQRSTUVXYZ-'
)
select 
 replace(
  translate(
   input,
   unwanted,
   replicate(' ', len(unwanted))
  ),
  ' ', ''
 ) 
from t

In other DBMSes the third argument can be shorter, and the characters for which there are no replacements will simply be deleted, making this approach even less complicated. For example, in Postgres:

with t (s) as (select 'BO2003056-2')
select 
 translate(s, 'ABCDEFGHIJKLMNOPQRSTUVXYZ-', '')
from t
1
  • 1
    Love when I see a good use case for TRANSLATE() in the wild. Might want to mention how the 3rd parameter is required to have the same number of characters as the 2nd parameter (or add a link to the docs). Note for OP, if there's any other non-numeric characters in the column besides alpha-characters and dashes, you'll need to add them to the 2nd parameter of the TRANSLATE() function too.
    – J.D.
    Jun 13 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.