This is from my Common TSQL Mistakes SQL Saturday session, which I have presented over 50 times now at various venues. Hopefully you can adapt it to your needs (if you haven't already found another solution like simply removing the unwanted data or adding a new column that is just numeric, etc.

KEY TAKEAWAYS

1) Remember that the optimizer can do almost anything it wants with your query as long as algebraically and logically it gives you the same output/effect. I note this may not be the effect you desire! 🙂

2) Learn about and use the power of the CASE keyword!

3) Try to not architect multi-variant columns! 😉

Use tempdb

set nocount on

go

IF OBJECT_ID(N'Accounts', N'U') IS NOT NULL

DROP TABLE dbo.Accounts;

CREATE TABLE dbo.Accounts (

account_nbr INT NOT NULL PRIMARY KEY,

account_type VARCHAR(20) NOT NULL

CHECK (account_type IN ('Personal', 'Business Basic', 'Business Plus')),

account_reference VARCHAR(30) NOT NULL);

INSERT dbo.Accounts VALUES(1, 'Personal', 'abc');

INSERT dbo.Accounts VALUES(2, 'Business Basic', '101');

INSERT dbo.Accounts VALUES(3, 'Personal', 'def');

INSERT dbo.Accounts VALUES(4, 'Business Plus', '5');

SELECT account_nbr, account_type, account_reference

FROM dbo.Accounts;

SELECT account_nbr, account_reference AS account_ref_nbr

FROM dbo.Accounts

WHERE account_type LIKE 'Business%'

AND CAST(account_reference AS INT) > 20;

Error:

Conversion failed when converting the varchar value 'abc' to data type int.

SELECT account_nbr, account_ref_nbr

FROM (SELECT account_nbr,

CAST(account_reference AS INT) AS account_ref_nbr

FROM dbo.Accounts

WHERE account_type LIKE 'Business%') AS A

WHERE account_ref_nbr > 20;

Error:

Conversion failed when converting the varchar value 'abc' to data type int.

SELECT account_nbr, account_reference AS account_ref_nbr

FROM dbo.Accounts

WHERE account_type LIKE 'Business%'

AND CASE WHEN account_reference NOT LIKE '%[^0-9]%'

THEN CAST(account_reference AS INT)

END > 20;

SELECT account_nbr, SUM(CAST(account_reference AS INT)) AS account_total

FROM dbo.Accounts

WHERE account_type LIKE 'Business%'

AND CASE WHEN account_reference NOT LIKE '%[^0-9]%'

THEN CAST(account_reference AS INT)

END > 20

GROUP BY account_nbr

DROP TABLE dbo.Accounts;