Query issue

Question

Post reply

Query issue

kk.86manu

Ten Centuries

Points: 1268
More actions
April 25, 2013 at 9:34 am

#394672

Hi All,
I have a table with the following values
Alexander Glaesel
Alexander Glæsel
Alexander Riess
Alexander Rieß
I am running the following query select name from table A group by name having count(1)>1.The output seems to be returning all the above rows though all the records are not similar.
Can anyone explain why this is happening?
Your help would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Can you post ddl and sample data? Based on your vague description I put this together.

with something (SomeValue) as

(

select 'Alexander Glaesel' union all

select 'Alexander Glæsel' union all

select 'Alexander Riess' union all

select 'Alexander Rieß'

)

select SomeValue

from something

group by SomeValue

having COUNT(SomeValue) > 1

It returns 0 rows as you would expect.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

collation.

for case insensitive searches, some high ascii characters are treated as the same character as certain low ascii.

you can use an explicit binary collation, for example, which would seperate teh items with high ascii:

select descrip COLLATE Latin1_General_BIN

from YourTable

group by descrip

i keep this little note to myself for some of the more common substitutions i trip over on my side:

ÀÁÂÃÄÅ -->A 192 to 197
Æ -->AE 198
Ç -->C 199
ÈÉÊË -->E 200 to 203
ÌÍÎÏ -->I 204 to 207
Ð -->D 208
Ñ -->N 209
ÒÓÔÕÖ -->O 210 to 214
× -->x 215 (multiplication operator)
Ø -->0 216 zero or capital "o"?
ÙÚÛÜ -->U 217 to 220
Ý -->Y 221
Þ --> 222
ß --> 223
àáâãäå -->a 224 to 229
æ -->ae 230
ç -->c 231
èéêë -->e 232 to 235
ìíîï -->i 236 to 239
ð --> 240
ñ -->n 241
òóôõö -->o 242 to 246
÷ -->/ 247 (division operator)
ø -->0 248 zero or capital "o"?
ùúûü -->u 249 to 252
ý -->y 253
þ --> 254
ÿ -->y 255

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Lowell (4/25/2013)
collation.

Thanks Lowell. I figured it was collation but wasn't totally sure how to demonstrate it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/