45

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
Improve this question
2
  • 1
    In the table UserID column have only Integer value or any Varchar Value.. For eg : 122 like this OR 122Adf like this...? Commented Apr 12, 2013 at 18:09
  • 1
    UserID column has only Integer value. Thanks! Commented Apr 12, 2013 at 18:30

5 Answers 5

Reset to default
26

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) > 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 <= 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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I tried "SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), '')) FROM dbo.[audit];", but it doesn't work. I also tried LTRIM and RTRIM, but doesn't work either... Thanks for help
@Milacay well, are you sure CHAR(0) is the only invalid value in the column? Please identify a single row where this fails, and then tell us the output of, e.g.: SELECT CONVERT(VARBINARY(32), UserID) FROM dbo.[audit] WHERE LEFT(UserID, 4) = '1581';
I ran your query with "varbinary(32)..." and the output is : 0x31353831000000000000. BTW, this varchar field (userID) was converted from Varbinary(128) to Varchar(128), and now I want to convert it to INT to Join with the UserID on different tables... Thanks @Aaron
Thank you very much @Aaron. The "SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), '')) FROM @foo;" works. I apologize for the confusion.
Why was there any conversion to varbinary in the first place? Also note that converting from varbinary to varchar can have different results. Try SELECT CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo')), CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo'),1), LEN(CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo'))), LEN(CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo'),1));
|
24

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.

Improve this answer

2 Comments

Would you please explain why adding .0e0 makes it numeric?
@Tigran it doesn't make it numeric. Appending this helps validate whether a string that was already numeric is an integer or not. E.g. 123 would pass when written as 123.0e0 but 123.45 would fail as 123.45.0e0 is not valid.
3

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
Improve this answer

Comments

2

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
Improve this answer

1 Comment

I tried all that LTRIM, RTRIM, and replace char(0) with '', but doesn't work either. Weird! thanks.
0

This is how I solved the problem in my case:

First of all I made sure the column I need to convert to integer doesn't contain any spaces:

update data set col1 = TRIM(col1)

I also checked whether the column only contains numeric digits.
You can check it by:

select * from data where col1 like '%[^0-9]%' order by col1

If any nonnumeric values are present, you can save them to another table and remove them from the table you are working on.

select * into nonnumeric_data from data where col1 like '%[^0-9]%' delete from data where col1 like '%[^0-9]%'

Problems with my data were the cases above. So after fixing them, I created a bigint variable and set the values of the varchar column to the integer column I created.

alter table data add int_col1 bigint update data set int_col1 = CAST(col1 AS VARCHAR)

This worked for me, hope you find it useful as well.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.