Return to Answer

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 arguments (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 

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 

Something like this, may be:

with t (s) as (select 'BO2003056-2') select replace( translate( s, '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 arguments (characters to be replaced).

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 

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 arguments (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 

Something like this, may be:

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

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

Something like this, may be:

with t (s) as (select 'BO2003056-2') select replace( translate( s, '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 arguments (characters to be replaced).

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