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 

Answer 1

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.