Unexpected Result of Select Statement: Shubham Saxena

Question

Unexpected Result of Select Statement: Shubham Saxena

shubham.saxena67

Default port

Points: 1490
More actions
September 23, 2012 at 6:33 am

#282032

Hi Friends,
I am getting unexpected result:Please Explain deeply.
SELECT CAST(643 AS VARCHAR(1))
select Cast(45 as char(1))
Result:
*
why i am getting result as * .what does it mean? what is the logic behind unexpected result..
please send me answer as soon as possible....
Thanks!!
Regards,
Shubham Saxena

Viewing 7 posts - 1 through 6 (of 6 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

This is documented in the Truncating and Rounding Results section of CAST and CONVERT in Books Online. The * means 'Result length too short to display'. This odd behaviour is maintained for backward compatibility with old versions of SQL Server. The more modern types nchar and nvarchar return an error instead:

SELECT CAST(643 AS nvarchar(1));

SELECT CAST(45 as nchar(1));

[font="Courier New"]Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type nvarchar.[/font]

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Expanding upon SQL Kiwi reply - read this

http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

Scroll down to about the middle of the page to see the explanation.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

bitbucket-25253 (9/23/2012)
Scroll down to about the middle of the page to see the explanation.

That's just the SQL Server 2000 version of the same Books Online link to CAST and CONVERT I have in my reply 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Hmmm... deja vu

http://www.sqlservercentral.com/Forums/FindPost1362083.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

http://blog.sqlauthority.com/2012/09/20/sql-server-2-t-sql-puzzles-and-win-usd-50-worth-amazon-gift-card-and-25-other-prizes/[/url]

Quick, $50 for the correct answer 🙂

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 6

Dave Ballantyne (9/23/2012)
http://blog.sqlauthority.com/2012/09/20/sql-server-2-t-sql-puzzles-and-win-usd-50-worth-amazon-gift-card-and-25-other-prizes/[/url]
Quick, $50 for the correct answer 🙂

Sigh. I suggest these for part 2:

SELECT COUNT(*)

SELECT DAY('')

SELECT DB_ID() -- in master database only

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi