July 15, 2005 at 1:12 am
It's my impession too that an rtrim is performed when comparing strings. That's why strange things may happen:
declare
@a varchar(1),
@b-2 varchar(1),
@C varchar(1)
select @a = ''
select @b-2 = ' '
select @C = 'x'
select 'a = b' -- returns a = b
else
select 'a != b'
select 'a+c = b+c'
else
select 'a+c != b+c' -- returns a + c != b + c
Thus a = b, but if you add c to both sides of the equation, you get different results (and this time, my db is on 80 level :rolleyes.
Also select charindex(@a, ' ') and select charindex(@b, ' ') are different. But len(@a) = len(@b) = 0.
I would definitely have preferred that '' and ' ' were not equal. Does anyone know a reason why they are equal?
July 15, 2005 at 5:19 am
Check out Farrell Keough's answer on the previous page.
July 15, 2005 at 5:36 am
Oh, I skipped that one after reading the first two lines
I don't understand his answer (that the result is unknown). Since '' = ' ' returns true and '' != ' ' returns false it looks very much as if they are equal (i.e the result is known). (And unlike what he writes, they both have length 0 in my db).
July 15, 2005 at 5:46 am
Steve
We need men who can dream of things that never were.
July 15, 2005 at 6:26 am
Can you post the image on the net.. we can't see squat if it's on your HD.
Also what is that message supposed to mean?
July 15, 2005 at 9:05 am
Steve
We need men who can dream of things that never were.
July 15, 2005 at 9:13 am
"Also what is that message supposed to mean? - It's just a quick quote. How many times have you posted a reply to a query, in all its full technical glory - only for the recipient to interpret it incorrectly and act on their interpretation of your reply? Requiring you to put them straight again....."
Too often... and I don't see that stopping anything soon.
"I have seen this in here on a number of occaisions and felt the quote would be appreciated and a humourous end to another week."
Always a welcomed way to end the week .
July 15, 2005 at 9:17 am
I thaught I could explain it in my own words but I'm still not sure... maybe you should start another thread for this one as my week-end is about ready to kick in .
July 15, 2005 at 9:31 am
From BOL:
Trailing blanks are ignored in comparisons in non-Unicode data; for example, these are equivalent:
WHERE au_lname = 'White'WHERE au_lname = 'White 'WHERE au_lname = 'White' + SPACE(1)
and "If the compatibility level is 65 or lower, SQL Server interprets empty strings as single spaces."
Also, the ANSI_PADDING setting controls whether trailing blanks are stripped from varchar fields (and
trailing zeroes from varbinary). This doesn't affect comparisons though.
July 15, 2005 at 9:47 am
Where did you get that in books online... This is not listed in mine under set ainsi_padding???
July 15, 2005 at 9:55 am
It's only odd on the surface: when two strings are compared in T-SQL, the shorter one is first padded with spaces at the right, to make them equal in length, then they are compared.
So '' = ' ' really goes '' changed to ' ' then tested against ' '.
This is why you can compare a char(5) and a char(10) for equality.
Try
if 'a' = 'a '
select 'equal'
else
select 'not equal'
for example.
July 15, 2005 at 9:56 am
The first quote is from the topic "Comparison Search Conditions"
The '' = ' ' issue can be found under "sp_dbcmptlevel" or "Empty Strings (Level 2)"
ANSI_PADDING is discussed under "Setting Database Options"
The issue of trailing spaces in comparisons is not discussed under ANSI_PADDING because that setting only affects storing data in varchar fields. I believe it will ignore trailing spaces whether ANSI_PADDING is ON or OFF.
July 15, 2005 at 9:57 am
Thanx for the info... If that doesn't settle it, we'll have to open a case with Microsoft .
July 15, 2005 at 2:57 pm
Now I am a Bully, not a BullDozer? sheeesh..., try and joke around.
(Good picture though...).
I wasn't born stupid - I had to study.
July 15, 2005 at 7:21 pm
I thought you'd have changed your avatar by now .
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply