SQL Server 2012 and Later

Just use Try_Convert instead:

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Read more about Try_Convert.

SQL Server 2008 and Earlier

The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

However, I like using strategies such as this with SQL Server 2005 and up:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.

You may be interested to read Erland Sommarskog's Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.

There are additional issues here but I can't address them now.

P.S. I had a brainstorm today. An alternate to the "traditional way" that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I've noticed that since learning CROSS/OUTER APPLY I've improved my query capability with older SQL Server versions, too--as I am getting more versatile with the "outer reference" capabilities of SELECT, ON, and WHERE clauses!)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

It's a lot shorter than the CASE statement.

I tried a lot of things to fix it, and no success.

I have a field called MASTER_BOL_NUMBER . According to documentation it is CHAR.

I see that inside it has only blanks and numbers

When I try to CAST( MASTER_BOL_NUMBER as numeric)

I am getting an error “Error converting data type varchar to numeric.”

I tried also smth like that

CAST( IIF(MASTER_BOL_NUMBER='',0,MASTER_BOL_NUMBER) as numeric)

and this

CAST( IIF(MASTER_BOL_NUMBER IS NULL,0,MASTER_BOL_NUMBER) as numeric)

Also no success, I really don’t understand why I am getting this error because usually CAST or CONVERT fix this data type issues.

Does someone else know what may help but for those functions?

Since you are returning numeric and non umeric values you shoud cast everything back to varchar. Also use try_cast function because isnumeric function will return sometimes true when you have dollar sign in your string for example and convert function will fail:

SELECT ID,
       CASE WHEN TRY_CAST(COL_VALUE AS NUMERIC) IS NOT NULL 
            THEN CAST(CAST(COL_VALUE AS NUMERIC) * 1000 AS VARCHAR(100))
            ELSE COL_VALUE 
       END AS [COL_VALUE]
FROM Table

The error is telling you the conversion is happening in the statement that starts in line 1.

As for why, it's because '' cannot be converted to a numeric but it can be converted to an int.

SELECT CONVERT(decimal(2,1),'')
--Error converting data type varchar to numeric.
GO
SELECT CONVERT(int,'')
--
GO

You were previously converting it to an int (the literal 1 is an int). Get out of the habit of declaring '' for a number; it is by definition not a number, it is a zero length string. If you want zero, then use 0.

Hello,

I am not sure why but I have a difficult time selecting fields that have a value ‘X’ in one of the fields.

I have a field wf.SMP_FLAG where I should have only two values ’ ’ (blank) and ‘X’ (letter X). I am not sure what kind of data type it is in the system, but I am constantly getting an error “Error converting data type varchar to numeric.”

This field is defined in CTE when in one of the tables I type ‘X’ AS ‘SMP_FLAG’ and in the next CTE I am selecting all the fields that are of ‘X’ type. I am getting an error:

Error converting data type varchar to numeric.

When I am selecting WHERE wf.SMP_FLAG = ‘X’ (in the next SELECT statement)

I am getting an error " Error converting data type varchar to numeric. "

I tried to change it to such queries

– WHERE wf.SMP_FLAG = 'X’ – Error converting data type varchar to numeric.
– WHERE CAST(wf.SMP_FLAG as char) = ‘X’ – Error converting data type varchar to numeric.
– WHERE CAST(wf.SMP_FLAG AS numeric) = ‘X’ – Error converting data type varchar to numeric.
– WHERE CONVERT(VARCHAR(50), wf.SMP_FLAG) = ‘X’ – Error converting data type varchar to numeric.

When I

TRY_CAST(wf.SMP_FLAG as float) = ‘X’ I am not getting errors but everything is blank as if there were no ‘X’ (but they were in the previous CTE).

Still no luck. Maybe someone may have an idea.