Post reply

Casting question

  • March 15, 2013 at 3:31 am

    #1597398

    ah ... this was soo easy, i just selected the answer in one click....:Whistling: but selected the wrong one. (interesting read, so far)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • March 15, 2013 at 4:08 am

    #1597408

    kapil_kk (3/15/2013)

    Danny Ocean (3/15/2013)

    Really a nice and good question.

    one more thing apart from this question. if you set ARITHABORT OFF and then execute below code, it will give output as "*" due to insufficient char length. and if you set char length more than 7, it will give "0.111111".

    It denotes that we need to provide an insufficient length whenever we convert values in char or varchar.

    SET ARITHABORT OFF

    select isnull ( convert (char(3),1.0/9) ,'*')

    Vinay, I tried with ARITHABORT OFF but still I am getting same error "Arithmetic overflow error converting numeric to data type varchar."

    set arithabort off

    select ISNULL(CONVERT(char(3),1.0/9),'*')

    Kapil, you need to SET ANSI_WARNINGS OFF. Look at below two examples. Example 1 execute successfully but example 2 give an error.

    ---- Example 1

    SET ANSI_WARNINGS OFF

    SET ARITHABORT OFF

    GO

    select isnull ( convert (char(3),1.0/9) ,'*') ,isnull ( convert (char(8),1.0/9) ,'*')

    GO

    SET ARITHABORT ON

    SET ANSI_WARNINGS ON

    ---- Example 2

    SET ANSI_WARNINGS ON

    GO

    select isnull ( convert (char(3),1.0/9) ,'*') ,isnull ( convert (char(8),1.0/9) ,'*')

    GO

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • March 15, 2013 at 4:40 am

    This was removed by the editor as SPAM

  • March 15, 2013 at 4:44 am

    #1597417

    Nice question, thanks. It made me think - I better rest now ...... 😉

    Dave Morris :alien:

    "Measure twice, saw once"

  • March 15, 2013 at 5:25 am

    #1597435

    Danny Ocean (3/15/2013)

    kapil_kk (3/15/2013)

    Danny Ocean (3/15/2013)

    Really a nice and good question.

    one more thing apart from this question. if you set ARITHABORT OFF and then execute below code, it will give output as "*" due to insufficient char length. and if you set char length more than 7, it will give "0.111111".

    It denotes that we need to provide an insufficient length whenever we convert values in char or varchar.

    SET ARITHABORT OFF

    select isnull ( convert (char(3),1.0/9) ,'*')

    Vinay, I tried with ARITHABORT OFF but still I am getting same error "Arithmetic overflow error converting numeric to data type varchar."

    set arithabort off

    select ISNULL(CONVERT(char(3),1.0/9),'*')

    Kapil, you need to SET ANSI_WARNINGS OFF. Look at below two examples. Example 1 execute successfully but example 2 give an error.

    ---- Example 1

    SET ANSI_WARNINGS OFF

    SET ARITHABORT OFF

    GO

    select isnull ( convert (char(3),1.0/9) ,'*') ,isnull ( convert (char(8),1.0/9) ,'*')

    GO

    SET ARITHABORT ON

    SET ANSI_WARNINGS ON

    ---- Example 2

    SET ANSI_WARNINGS ON

    GO

    select isnull ( convert (char(3),1.0/9) ,'*') ,isnull ( convert (char(8),1.0/9) ,'*')

    GO

    Yes, It works....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • March 15, 2013 at 5:31 am

    #1597438

    Thanks for the question!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 15, 2013 at 5:43 am

    #1597446

    Thanks for the question. Great Friday question to end the week!

  • March 15, 2013 at 6:38 am

    #1597470

    Christian Buettner-167247 (3/15/2013)

    Good question but the correct answer is "0 " (with 2 trailing spaces), not "0".#

    Edit: Add example:

    SELECT REPLACE(ISNULL ( CONVERT (char(3),1/9),'*'),' ','<Space>') AS Ret

    0<Space><Space>

    That's correct. Convert to varchar to remove the trailing spaces. 🙂

    --------------------------------------------------------------------------------------
    Hai Ton
    My Db4Breakfast blog.

  • March 15, 2013 at 7:37 am

    #1597491

    okbangas (3/15/2013)

    Really easy question, but the explanation puzzles me. As far as I can see it is no implicit conversion, just a pure integer division.

    I assume it is a reference to the 1 and the 9 being interpreted as integers.

  • March 15, 2013 at 9:50 am

    #1597563

    Good question and thanks!

    Not all gray hairs are Dinosaurs!

  • March 15, 2013 at 10:03 am

    #1597566

    Thanks - EZPZ

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 15, 2013 at 11:28 am

    #1597602

    I guess I'm the only one that initially looked for '1/9' as an answer choice. Doh.

  • March 15, 2013 at 11:41 am

    #1597606

    Nice question, but it's a pity that the correct answer isn't any of the options. I picked '0' as it's the least wrong, but it definitely is wrong. The correct answer is '0 ' (two trailing spaces) and '0' isn't even a possible value for a char(3) object.

    Tom

  • March 15, 2013 at 2:53 pm

    #1597696

    Technically the answer is '0 ', but it was easy enough to deduce the answer choice that the author was looking for. If "None of the above" had been a choice then I imagine the comments on this thread would be much more "spirited". 🙂

    Good question overall. Thanks Eli!

  • March 15, 2013 at 4:03 pm

    #1597749

    Nice question, make us thinking.

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

Viewing 15 posts - 16 through 30 (of 34 total)

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