I've got some code that is supposed to generate a unique key by prefixing an integer with a single character. It's been working fine, but now the integer input is longer than I want for the final column and the result isn't what I expected at all. Both the columns in the result below yield "*". I would have expected CAST to fail and TRY_CAST to return NULL. Can anyone explain what's going on please?
SELECT CAST(10000137 AS VARCHAR(7)), TRY_CAST(10000137 AS VARCHAR(7))
October 15, 2024 at 4:20 pm
That is SQL's method for handling insufficient space on a CAST.
The only safe method would be to use 11 chars. 10 max digits plus the prefix char (assuming you don't allow negative numbers).
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 16, 2024 at 6:18 am
That is SQL's method for handling insufficient space on a CAST.
I can see why CAST might do that (although I'd expect it to cause an error instead), but why doesn't TRY_CAST give a NULL? That's what the documentation says it will do if the target datatype cannot accept the value.
ScottPletcher wrote:That is SQL's method for handling insufficient space on a CAST.
I can see why CAST might do that (although I'd expect it to cause an error instead), but why doesn't TRY_CAST give a NULL? That's what the documentation says it will do if the target datatype cannot accept the value.
Because the target datatype *can* accept the value - it is considered 'Too short to display'. Instead of showing an error - or truncating the data, SQL Server returns the *.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply