SQL Server select with equal criteria returns unexpected results 2

[Jaderwannabe]

select asset_id from asset_oth_bk_depr where b3_depr_mthd_cd = 'SL8' order by asset_id commit
select asset_id from asset_oth_bk_depr where b3_depr_mthd_cd = 'SL8 ' order by asset_id commit
select asset_id from asset_oth_bk_depr where b3_depr_mthd_cd = 'SL8  ' order by asset_id commit
select asset_id from asset_oth_bk_depr where b3_depr_mthd_cd = 'SL8   ' order by asset_id commit
select asset_id from asset_oth_bk_depr where b3_depr_mthd_cd = 'SL8    ' order by asset_id commit

recently searching for a type-o in the cd because I suspected a space after the SL8, the first select is the one I ran, since it returned 65 records - I thought I was wrong- turned out I wasnt. In trying to figure out why I was misled I tried the 5 statements above. all of them returned 65 rows- all the same assets. I thought the = meant it had to be exact, what criteria would cause this to happen?
thank you.

 

[LNBruno]

What's your data type and length for "b3_depr_mthd_cd"?

< M!ke >
Acupuncture Development: a jab well done.

 

[Crowley16]

why not use trim?

i.e.

select fieldName from tableName where rtrim(fieldName) = 'SL8'

--------------------
Procrastinate Now!

 

[ca8msm]

That's the default for T-SQL:

http://support.microsoft.com/kb/316626

To prove it, run this:

DECLARE @TEST TABLE (id Int, Description varchar(10))

INSERT @TEST VALUES (1, 'TEST1')
INSERT @TEST VALUES (1, 'TEST1 ')
INSERT @TEST VALUES (1, 'TEST1  ')
INSERT @TEST VALUES (1, 'TEST1   ')
INSERT @TEST VALUES (1, 'TEST1    ')

SELECT id, description, len(description) FROM @TEST WHERE description = 'TEST1'
SELECT id, description, len(description) FROM @TEST WHERE description = 'TEST1 '
SELECT id, description, len(description) FROM @TEST WHERE description = 'TEST1  '
SELECT id, description, len(description) FROM @TEST WHERE description = 'TEST1   '
SELECT id, description, len(description) FROM @TEST WHERE description = 'TEST1    '

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[SQLBI]

I was going to suggest using TRIM(), but then i tested the scenario and got the same result.

create table test (text varchar(255))
go

insert test (text) values('SL8')
insert test (text) values('SL8 ')
insert test (text) values('SL8  ')
insert test (text) values('SL8   ')
insert test (text) values('SL8    ')

select *
from test
where text = 'SL8'

Whilst TRIM would certaily solve the problem, i'd also like to know why = doesn't exactly match.



Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[gmmastros]

Trailing spaces are not considered when doing searches. This is the way it's always been.

If you want to differentiate between the number of spaces that appear at the end of the field, you could use the DataLength function. Like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a '[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a  '[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a   '[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a    '[/color])


[COLOR=blue]Select[/color] Len(Data) [COLOR=blue]As[/color] Len_Of_Data, 
       [COLOR=#FF00FF]DataLength[/color](Data) [COLOR=blue]As[/color] Data_Len_Data, 
       Data
[COLOR=blue]From[/color]   @Temp 
[COLOR=blue]Where[/color]  Data = [COLOR=red]'a'[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jaderwannabe]

LNBruno: it is defined as varchar(6),NULL

Crowley16: I was searching for the ones that were entered incorrectly 'SL8' is right 'SL8 ' is not. So I was looking for all the correctly entered ones. in essance the statement did the rtrim for me when i didnt want it to.

 

[SQLBI]

... and there's the answer, thanks ca8msm!


Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[ca8msm]

Another MS article that may be of use to you is:

http://support.microsoft.com/kb/231830/EN-US/

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Jaderwannabe]

Thanks for the fast responses and the lit links.. i'll check them out.
Learn something new everyday- usually the hard way ;-)