SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL


TSQL

Author
Message
john.arnott
john.arnott
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7046 Visits: 3059
malleswarareddy_m (4/4/2010)
Good QOD.This Question gives good information about bit datatypes.
when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one.
. . . .

This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero
DECLARE @MyNum int
SET @MyNum = ' '
-- attempt arithmetic operation
Select @myNum * 4
-- result is 0, not an error.


rjv_rnjn
rjv_rnjn
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1745 Visits: 431
john.arnott (4/5/2010)

This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero
DECLARE @MyNum int
SET @MyNum = ' '
-- attempt arithmetic operation
Select @myNum * 4
-- result is 0, not an error.


Good point, but I would like it more if SQL Server throws an exception and let developers know upfront that it's wrong to assign string/character values to an integer data type.
KevinC.
KevinC.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1079 Visits: 504
It will throw an error if you attempt to assign a NON-empty string to an integer data type.

I can see your point for an empty string/character value, but it does come in handy when you need to work with data where someone has set all the columns that should be integer to text Crazy
malleswarareddy_m
malleswarareddy_m
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3823 Visits: 1189
vk-kirov (4/5/2010)
malleswarareddy_m (4/4/2010)
declare @bit bit
set @bit='00000'
select @bit

This gives 1 as output.

This must be a typo because the result is 0.




Yes it will give zero as ouptut. Some typing mistake.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
malleswarareddy_m
malleswarareddy_m
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3823 Visits: 1189
john.arnott (4/5/2010)
malleswarareddy_m (4/4/2010)
Good QOD.This Question gives good information about bit datatypes.
when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one.
. . . .

This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero
DECLARE @MyNum int
SET @MyNum = ' '
-- attempt arithmetic operation
Select @myNum * 4
-- result is 0, not an error.



I think it will throw error when converting it to string datatype except(TRUE/False)

Malleswarareddy
I.T.Analyst
MCITP(70-451)
ziangij
ziangij
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4474 Visits: 380
good question, thanks :-)
john.arnott
john.arnott
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7046 Visits: 3059
malleswarareddy_m (4/5/2010)
john.arnott (4/5/2010)
malleswarareddy_m (4/4/2010)
Good QOD.This Question gives good information about bit datatypes.
when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one.
. . . .

This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero
DECLARE @MyNum int
SET @MyNum = ' '
-- attempt arithmetic operation
Select @myNum * 4
-- result is 0, not an error.



I think it will throw error when converting it to string datatype except(TRUE/False)

Sorry?? What will cause an error?

Your statement looks to be reversed of what you may have meant -- aren't we talking about converting from strings to numerics? My example of implicit conversion from a string to an int does not cause an error, nor would an empty string, the point being that it's not just conversions to the bit type where empty or blank strings result in zero.

I didn't find a BOL or MSDN article on this specific behavior, but from what I did find, it seems clear that the SQL engine sets the target to zero, then adds the numeric evaluation of the string, ignoring spaces. With nothing else to evaluate, the result remains zero.

You say you "think it will throw error". Did you try it?
malleswarareddy_m
malleswarareddy_m
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3823 Visits: 1189
john.arnott (4/6/2010)
malleswarareddy_m (4/5/2010)
john.arnott (4/5/2010)
malleswarareddy_m (4/4/2010)
Good QOD.This Question gives good information about bit datatypes.
when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one.
. . . .

This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero
DECLARE @MyNum int
SET @MyNum = ' '
-- attempt arithmetic operation
Select @myNum * 4
-- result is 0, not an error.



I think it will throw error when converting it to string datatype except(TRUE/False)

Sorry?? What will cause an error?

Your statement looks to be reversed of what you may have meant -- aren't we talking about converting from strings to numerics? My example of implicit conversion from a string to an int does not cause an error, nor would an empty string, the point being that it's not just conversions to the bit type where empty or blank strings result in zero.

I didn't find a BOL or MSDN article on this specific behavior, but from what I did find, it seems clear that the SQL engine sets the target to zero, then adds the numeric evaluation of the string, ignoring spaces. With nothing else to evaluate, the result remains zero.

You say you "think it will throw error". Did you try it?


your Example is correct.when convert the bit into string it will thrown an error.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
muhandis
muhandis
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 101
NA
Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79232 Visits: 11400
vivek.yadav (4/7/2010)
vk-kirov,
You are worng...

declare @bit bit
set @bit='00000'
select @bit


will always return 0 as o/p.
Please if you can check again.

That's what he said. Your post claimed it returned 1. Read back and check. :-P
He did say it was probably just a typo.
Oh, and you spelt 'wrong' wrong - another typo? ;-) :-P



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search