The problem is obvious -- you have a string and you are treating it as a number. There are three possible reasons.

The first is an explicit conversion where the string is converted to a number. This should be easy to find. You can replace the explicit conversion with try_convert() in SQL Server 2012+.

The second is not much harder. The + operator is used both for addition and string concatenation. It often causes this type of problem. All numeric arguments need to be converted to strings. Or, change all code that does concatenation to use the CONCAT() function -- the arguments are automatically converted to strings.

The third is implicit conversion for numeric operations. If you have any other operations (or function calls) that require numeric values, then SQL Server will implicitly convert the values to numbers. This can be very hard to find. Try to write code that does not rely on implicit conversions.

Note: Trying to filter out bad values using a WHERE clause (even in a subquery or CTE) does not work.

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?

Yes, this has been seen before. Many times.

You did you not show the full query, but I guess it goes something like:

select @real_variable = isnull(convert(real,substring(value,2,datalength(value))),0)
from   tbl
-- maybe some joins too
where  

One should avoid inserting data without listing columns explicitly. Your error is the exact reason why.

INSERT INTO Product_Approval 
VALUES ('@Product_ID', '@Type', '@Name', @Price, '@Supplier_ID')

The order of columns in the table is different from the one in the INSERT statement.

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.