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.
Answer from Aaron Bertrand on Stack OverflowYou 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.
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.
Use the convert function.
SELECT CONVERT(varchar(10), field_name) FROM table_name
Use the STR function:
SELECT STR(field_name) FROM table_name
Arguments
float_expression
Is an expression of approximate numeric (float) data type with a decimal point.
length
Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.
decimal
Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.
source: https://msdn.microsoft.com/en-us/library/ms189527.aspx
sql server - Cast varchar into int, converting any value under 0 to 0 - Database Administrators Stack Exchange
Best practice : converting INT to VARCHAR
convert varchar to int – SQLServerCentral Forums
Convert VARCHAR to INT ?
As TINYINT only allows 0-255 you could also use
ISNULL(TRY_CAST(YourCol AS TINYINT),0)
The TRY_CAST above returns NULL if supplied an invalid input for the datatype (including -ve numbers) and I would be minded to leave it that way.
But if you must change these to 0 you can wrap it in an ISNULL
Try something like this
DECLARE @BadData VARCHAR(100) = '-5'
DECLARE @TinyInt TINYINT
SET @TinyInt = CASE
WHEN cast(@BadData AS INT) < 0
THEN 0
ELSE @BadData
END
SELECT @TinyInt
Is there any method to convert VARCHAR to INTEGER?
CAST and CONVERT failed because we have alphanumeric values in the data.
Trying to find if there is a way to convert VARCHAR to INT without losing alphanumeric characters
Example data looks like 56H543G in varchar data type, when converted to integer I would like to see 56H543G ....is this possible
Assuming your first table is named A with a column named MyIntColumn of type int and your second table is named B with a column named MyNVarcharColumn of type nvarchar, you could do
From A
Inner Join B On A,MyIntColumn = Try_Cast(B.MyNVarcharColumn As int)
Tom
Hi All,
I faced the same issue whenever a maintenance job was done by Azure Managed Service. The strange thing is that I got this exception from my services, and it was only resolved after a restart.
What happened in between?