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

All we need is an easy explanation of the problem, so here it is.

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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply