Casting question

Question

Casting question

March 15, 2013 at 4:40 am

This was removed by the editor as SPAM

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

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 1

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.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

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

seatedElephant Say Hey Kid Points: 674 More actions · Answer 3

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

Dave Morris :alien:

"Measure twice, saw once"

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

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/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

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

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 6

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

db4breakfast Ten Centuries Points: 1097 More actions · Answer 7

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.

sestell1 SSChampion Points: 10230 More actions · Answer 8

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.

Miles Neale SSChampion Points: 13147 More actions · Answer 9

Good question and thanks!

Not all gray hairs are Dinosaurs!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

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

Rose Bud Hall of Fame Points: 3147 More actions · Answer 11

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

TomThomson SSC Guru Points: 104773 More actions · Answer 12

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

Ken SSCoach Points: 17191 More actions · Answer 13

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!

chgn01 SSCarpal Tunnel Points: 4356 More actions · Answer 14

Nice question, make us thinking.

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