I think I found SQL 2005 bug by doing simple Group BY!

I did:

select city,count(*)from classifieds_addressgroupby city

You would think it would distiguish between"Koh Phangan" and "Koh Phangan ". No!!! It shows them as one row in SQL!!! Is this SQL bug?

Aberdeen 1
bayombong 1
Brooklyn 4
Castle Peak Bay 1
Causeway Bay 2
Chiang Mai 1
Hong Kong 1
Hong Kong Wan Chai 1
Hong Kong Island 10
Hong Kong Sheung Wan 1
Karachi 1
Koh Phangan 21
Koh Samui 37
Koh Tao 9
Kowloon 13
Kowloon, Tsim Sha Tsui 1
Lantau Island 1
Mid-Levels 3
New Territories 2
New York 3
North Point 1
Oligar 1
Tsim Sha Tsui 1
Wan Chai 5
Yau Ma Tei 1
Yau Ma Tei, Kowloon 1

Actually I think it is worse than that! I run several queries and they all return 21 rows!!

select*from classifieds_addresswhere city='Koh Phangan'

select*from classifieds_addresswhere city='Koh Phangan '

select*from classifieds_addresswhere city='Koh Phangan '

select*from classifieds_addresswhere city='Koh Phangan '

Is not that a bug?? I am using SQL 2005 and the latest service pack!

|||

Seems MS is well aware of this problem:http://support.microsoft.com/kb/316626

I wonder why they do not fix this.

How do I select UNIQUE values (if there are spaces it is different than the one without spaces) and count of those records??

Of now is to late to do SET ANSI_PADDING OFF, since the values are already inserted with trailing spaces top the table...How do I distinguish those records??

|||

So for now you can do:

GROUP BY LTRIM(RTRIM(city))

|||

Jack,

That is not what I look for, I just want the oposite what you are proposing. What you are proposing is already done in group by inside of SQL Server...

I want to find the records where there are different. I want to find UNIQUE records in the table.I want to find those records with trailing spaces and mispells... I thought that Group By would do it, but it treats records with trailing spaces the same as the ones without...

GROUP BY LTRIM(RTRIM(city)) will just do the same as group by Rtrim(city)!

|||

Sorry for misunderstood your problem. Try the code below:

SELECT REPLACE(REPLACE(city,' ','_'),'_',' ')AS city,COUNT(*)FROM dbo.classifieds_addressGROUP BY REPLACE(city,' ','_')