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)
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 
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.