What is the fastest way to extract numeric characters from a field using TSQL?

Question

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)

Answer 1

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