September 30, 2023 at 2:54 am
When ASCII(Column) is null, what does it represent? Is this some kind of special/control characters? It's breaking my code and I'm getting below error
'Conversion failed when converting the varchar value '.' to data type int.'
Had used Bulk insert to load the data. Any idea if this is caused by bulk insert? How do I see the actual value to see what is in it?
Many thanks.
September 30, 2023 at 4:35 pm
The ASCII function clearly states it only handles valid ASCII values which are Integer Values and NULL which is a special value unto itself cannot be converted to an Integer thus it will always fail. So as a reminder here are the valid ASCII values
ASCII control characters (0-31 and 127)
ASCII printable characters (32-126) (most commonly referred to)
Extended ASCII characters (128-255)
The following is what you can do to fix your bug
CASE WHEN IncomingValue IS NULL
THEN ??
WHEN IncomingValue "IS NOT A VALID ASCII VALUE"
THEN ??
ELSE ASCII(IncomingValue)
END
Note however, I put ?? because the ASCII function returns a valid ASCII Integer and I have no clue what you want to do with a NULL value for the situations when it occurs or what you want to do (or even how you determine it without it breaking) when you recieve a non-valid ASCII character as I am guessing the rest of your scripts or code associated with that field will not handle a non-valid ASCII value.
Also all this information was easily found if you had taken the time to google it. Further if you are going to use something make sure you fully understand how what you are using is going to work otherwise you will always be chasing your tail when unexpected things occur that you should have coded for upfront.
October 1, 2023 at 4:05 pm
A NULL value should not cause an error, ASCII(NULL) should just return NULL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 1, 2023 at 4:55 pm
It isn't the ASCII function that's causing your error:
Try these statements:
SELECT ASCII('.');
SELECT ASCII(NULL);
SELECT ASCII(42);All of them return a valid value.
But these all cause the same error you are getting:
SELECT CAST('.' as INT);
SELECT CONVERT(INT, '.');
DECLARE @I as int = '.';
INSERT INTO your_table (int_column) VALUES ('.');Somewhere your code is trying to convert a full-stop into an int.
October 1, 2023 at 5:10 pm
Nice troubleshooting Jonathan AC Roberts I was unable to test that at the time of my post but great that you did.
October 1, 2023 at 7:18 pm
It isn't the ASCII function that's causing your error:
Try these statements:
SELECT ASCII('.');
SELECT ASCII(NULL);
SELECT ASCII(42);All of them return a valid value.
But these all cause the same error you are getting:
SELECT CAST('.' as INT);
SELECT CONVERT(INT, '.');
DECLARE @I as int = '.';
INSERT INTO your_table (int_column) VALUES ('.');Somewhere your code is trying to convert a full-stop into an int.
Plus 1000!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply