August 4, 2020 at 11:42 pm
This is for SQL Server 2016. Table called tblTest has a column called TestNumber that is varchar(5). I think this should work and does when I run it but I'm seeing references that it won't and can't figure out why. Can anyone explain or am I reading misinformation? Something about a conversion error but the cast should convert 20012 to varchar and work.
select * from tblTest
where TestNumber = cast(20012 as varchar(5))
August 4, 2020 at 11:58 pm
It's easier than that.
select * from tblTest
where TestNumber = '20012';
August 5, 2020 at 12:05 am
Yes, I did think of that too but the topic was about using cast and I was confused why I was reading cast didn't work when it does for me.
Thanks!
August 5, 2020 at 12:41 am
It's not that CAST doesn't work, it's that it's expensive, especially if you use it in a crazy place like a join.
August 5, 2020 at 12:48 am
This was a topic that I am covering for the 70-761 exam. The CAST isn't in a join and I read in the study guide that if performance isn't an issue, I can use less desired or older options, such as ISNULL vs COALESCE. This this was a simple example and there were no other requirements, so I can't understand why it wouldn't be acceptable.
August 5, 2020 at 3:17 am
wouldn't the answer you gave apply an internal cast? So wouldn't it be the same as if we specify an explicit cast? I mean, if the formats differ, it should be converted. No? I'm trying to learn this also. Thank you
August 5, 2020 at 3:20 am
Don't get into the habit of using implicit casting. If you're joining on two columns with different data types, one entire column has to be cast, and if you have a lot of records in your table, it's going to be painfully slow. (Now I see why Gail Shaw says she always tests her code on million+ row tables... because then bad performance is really obvious.)
August 5, 2020 at 11:31 am
Thanks for the input as it is appreciated.
Just to be clear, this is geared towards CAST questions I read about on the 70-761 exam that i am taking today and the blogger's explanation on the question doesn't work out for me. The table column is a varchar(5) and the query is comparing that to an int (20012) and getting a conversion error. CASTing the value (not the column) should resolve this as would wrapping a single quote around it. However, the blogger said both would results in an incorrect answer and that's why I'm asking. The example seemed straight forward and it made me question whether the blogger was correct about the answer.
I'm using Itzik Ben-Gan's exam ref book and it covers the topic of CAST but nothing specific like this.
August 5, 2020 at 1:38 pm
Not an error as such, but any leading zeros in the VARCHAR(5) column could cause issues.
DECLARE @x VARCHAR(5) = '01234';
IF @x = CAST(01234 AS VARCHAR(5))
SELECT 'Match';
ELSE
SELECT 'No match';
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 5, 2020 at 3:00 pm
It's not a problem for the number used in the question.
And there is no indication how a number "1234" would be correctly represented in tblTest. May be '01234' is not meant to be matched with a number 1234.
It would be useful to see exact formulation of the question. Devil is always in details.
_____________
Code for TallyGenerator
August 5, 2020 at 3:20 pm
Here is the full scenario:
You run the following query:
select * from tblTest
where TestNumber = 20012
The query output window displays the following error: "Conversion failed when converting the varchar value 'AB012' to a data type int.". You have to resolve this error. You modify the query to this:
select * from tblTest
where TestNumber = cast(20012 as varchar(5))
Does this meet the goal?
Yes and No are the only answers. I said yes but was told it was no.
August 5, 2020 at 3:45 pm
I agree with you: in this specific instance, it does meet the goal, though I would never do it that way (I'd use the method suggested by Pietlinden in the first response).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 5, 2020 at 4:50 pm
Yes, it works, it's easy enough to write code to prove it:
;with tbltest as (select cast('ab012' as varchar(5)) as TestNumber) select * from tblTest where TestNumber = cast(20012 as varchar(5))
Just trying to think of some way to justify a "no" answer, presumably the column would always be padded to 5 chars (which means it should be char(5) not varchar(5)), and thus the conversion should?! be:
right('0000' + cast(<numeric_value AS varchar(5)), 5)
Maybe?! that's what they meant. But that's not the actual q, and to me the answer is clearly YES.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 5, 2020 at 9:43 pm
Here is the full scenario:
You run the following query:
select * from tblTest
where TestNumber = 20012
The query output window displays the following error: "Conversion failed when converting the varchar value 'AB012' to a data type int.". You have to resolve this error. You modify the query to this:
select * from tblTest
where TestNumber = cast(20012 as varchar(5))
Does this meet the goal?
Yes and No are the only answers. I said yes but was told it was no.
From the text of the task it's clear that the goal is "to resolve this error". The error was about conversion, and nothing else.
the proposed modification of the query definitely resolves the error. So, the answer is 100% "Yes".
Whoever told you "no" - ask for an explanation. Then point on the logical error in it.
_____________
Code for TallyGenerator
August 5, 2020 at 9:53 pm
Yes, it works, it's easy enough to write code to prove it:
;with tbltest as (select cast('ab012' as varchar(5)) as TestNumber) select * from tblTest where TestNumber = cast(20012 as varchar(5))
Just trying to think of some way to justify a "no" answer, presumably the column would always be padded to 5 chars (which means it should be char(5) not varchar(5)), and thus the conversion should?! be:
right('0000' + cast(<numeric_value AS varchar(5)), 5)
Maybe?! that's what they meant. But that's not the actual q, and to me the answer is clearly YES.
Or STR(20012,5) ?
the question has no mention of the format used for storing numeric values in that varchar column. So, we can only speculate on this matter.
But - as you said - it's not the question.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply