[sql-server] SQL Server : converting varchar to INT

I am stuck on converting a varchar column UserID to INT. I know, please don't ask why this UserID column was not created as INT initially, long story.

So I tried this, but it doesn't work. and give me an error:

select CAST(userID AS int) from audit

Error:

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

I did select len(userID) from audit and it returns 128 characters, which are not spaces.

I tried to detect ASCII characters for those trailing after the ID number and ASCII value = 0.

I have also tried LTRIM, RTRIM, and replace char(0) with '', but does not work.

The only way it works when I tell the fixed number of character like this below, but UserID is not always 4 characters.

select CAST(LEFT(userID, 4) AS int) from audit

This is more for someone Searching for a result, than the original post-er. This worked for me...

declare @value varchar(max) = 'sad';
select sum(cast(iif(isnumeric(@value) = 1, @value, 0) as bigint));

returns 0

declare @value varchar(max) = '3';
select sum(cast(iif(isnumeric(@value) = 1, @value, 0) as bigint));

returns 3

This question has got 91,000 views so perhaps many people are looking for a more generic solution to the issue in the title "error converting varchar to INT"

If you are on SQL Server 2012+ one way of handling this invalid data is to use TRY_CAST

SELECT TRY_CAST (userID AS INT)
FROM   audit 

On previous versions you could use

SELECT CASE
         WHEN ISNUMERIC(RTRIM(userID) + '.0e0') = 1
              AND LEN(userID) <= 11
           THEN CAST(userID AS INT)
       END
FROM   audit 

Both return NULL if the value cannot be cast.

In the specific case that you have in your question with known bad values I would use the following however.

CAST(REPLACE(userID COLLATE Latin1_General_Bin, CHAR(0),'') AS INT)

Trying to replace the null character is often problematic except if using a binary collation.

I would try triming the number to see what you get:

select len(rtrim(ltrim(userid))) from audit

if that return the correct value then just do:

select convert(int, rtrim(ltrim(userid))) from audit

if that doesn't return the correct value then I would do a replace to remove the empty space:

 select convert(int, replace(userid, char(0), '')) from audit