Simple cast question

  • Mark Eckeard

    Hall of Fame

    Points: 3462

    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))

  • pietlinden

    SSC Guru

    Points: 62894

    It's easier than that.

    select * from tblTest
    where TestNumber = '20012';
  • Mark Eckeard

    Hall of Fame

    Points: 3462

    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!

  • pietlinden

    SSC Guru

    Points: 62894

    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.

  • Mark Eckeard

    Hall of Fame

    Points: 3462

    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.

  • Mysterio

    Old Hand

    Points: 360

    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

  • pietlinden

    SSC Guru

    Points: 62894

    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.)

  • Mark Eckeard

    Hall of Fame

    Points: 3462

    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.

  • Phil Parkin

    SSC Guru

    Points: 244733

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Sergiy

    SSC Guru

    Points: 109841

    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.

  • Mark Eckeard

    Hall of Fame

    Points: 3462

    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.

  • Phil Parkin

    SSC Guru

    Points: 244733

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • ScottPletcher

    SSC Guru

    Points: 98557

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Sergiy

    SSC Guru

    Points: 109841

    Mark Eckeard wrote:

    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.

  • Sergiy

    SSC Guru

    Points: 109841

    ScottPletcher wrote:

    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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply