SQL Server converting varchar to INT

Question

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

User · Answer

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

User · Answer

You could try updating the table to get rid of these characters   UPDATE dbo  audit    SET UserID   REPLACE UserID  CHAR 0         WHERE CHARINDEX CHAR 0   UserID   gt  0    But then you ll also need to fix whatever is putting this bad data into the table in the first place  In the meantime perhaps try   SELECT CONVERT INT  REPLACE UserID  CHAR 0          FROM dbo  audit     But that is not a long term solution  Fix the data  and the data type while you re at it   If you can t fix the data type immediately  then you can quickly find the culprit by adding a check constraint   ALTER TABLE dbo  audit    ADD CONSTRAINT do not allow stupid data   CHECK  CHARINDEX CHAR 0   UserID    0     EDIT  Ok  so that is definitely a 4-digit integer followed by six instances of CHAR 0   And the workaround I posted definitely works for me   DECLARE  foo TABLE UserID VARCHAR 32    INSERT  foo SELECT 0x31353831000000000000   -- this succeeds  SELECT CONVERT INT  REPLACE UserID  CHAR 0        FROM  foo   -- this fails  SELECT CONVERT INT  UserID  FROM  foo    Please confirm that this code on its own  well  the first SELECT  anyway  works for you  If it does then the error you are getting is from a different non-numeric character in a different row  and if it doesn t then perhaps you have a build where a particular bug hasn t been fixed   To try and narrow it down you can take random values from the following query and then loop through the characters   SELECT UserID  CONVERT VARBINARY 32   UserID    FROM dbo  audit    WHERE UserID LIKE     0-9       So take a random row  and then paste the output into a query like this   DECLARE  x VARCHAR 32    i INT  SET  x   CONVERT VARCHAR 32   0x      -- paste the value here SET  i   1  WHILE  i  lt   LEN  x  BEGIN   PRINT RTRIM  i            RTRIM ASCII SUBSTRING  x   i  1      SET  i    i   1  END   This may take some trial and error before you encounter a row that fails for some other reason than CHAR 0  - since you can t really filter out the rows that contain CHAR 0  because they could contain CHAR 0  and CHAR something else   For all we know you have values in the table like   SELECT  15    CHAR 9     23    CHAR 0        which also can t be converted to an integer  whether you ve replaced CHAR 0  or not   I know you don t want to hear it  but I am really glad this is painful for people  because now they have more war stories to push back when people make very poor decisions about data types

User · Answer

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   lt   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

User · Answer

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

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

Examples related to sql-server