CAST failed when converting the varchar value to int

advertisements

Microsoft SQL Server 2008 R2, getting an unexpected 'Conversion failed' error.

Not quite sure how to describe this problem, so here is a simple example. When I parse some column in the database like this:

SELECT CAST(RIGHT(CurLovim,LEN(CurLovim)-1) As INT) FROM
(
 SELECT SUBSTRING(...) as CurLovim
 FROM [BackOfficeFut].[dbo].[our_orders]
 WHERE isin_id IN
 (
  SELECT distinct isin_id
  FROM [BackOfficeOpt].[dbo].[opt_sess_contents]
  WHERE base_isin_id = 335568
 )
)as t1

I get an error:

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

But if I change the condition on isin_id this way (it's equivalent):

SELECT CAST(RIGHT(CurLovim,LEN(CurLovim)-1) As INT) FROM
(
 SELECT SUBSTRING(...) as CurLovim
 FROM [BackOfficeFut].[dbo].[our_orders]
 WHERE isin_id != 335568
)as t1

the query works well.

I find it very strange because if I remove CAST, i.e. make queries SELECT RIGHT(CurLovim,LEN(CurLovim)-1) FROM ... they return exactly the same result.

What causes this error? Is this a known issue?


Did you try testing the string to make sure it can be converted to a numeric?

Look at the isnumeric() function.

http://technet.microsoft.com/en-us/library/ms186272.aspx

-- Just play
USE tempdb;
GO

-- Create test table
CREATE TABLE T2
( C2 varchar(10) )
go

-- Insert data
INSERT INTO T2 VALUES ('a2b');
INSERT INTO T2 VALUES ('1ab');
INSERT INTO T2 VALUES ('ab3');
GO

-- Check column 2 before converting
SELECT
  CAST(
    CASE
      WHEN ISNUMERIC(SUBSTRING(C2, 2, 1)) = 0 THEN 0
      ELSE SUBSTRING(C2, 2, 1)
    END AS INT) AS N2
FROM T2;
GO

-- Remove the table
DROP TABLE T2;
GO