Sql Server 2005 Sorting when having both english letter 'e' and french letter 'é'

Question

Sql Server 2005 Sorting when having both english letter 'e' and french letter 'é'

eswaran_mca

SSC Rookie

Points: 45
More actions
August 4, 2010 at 5:49 am

#140122

Hi all,
I have user table name called tbl_Users in Sql Server 2005.
In this table I have column called
username varchar(30) sql_latin1_general_cp1_ci_ai null.
in this column i have two names for an example
1/ eswaran -> english letter "e"
2/ éswaran -> french letter "é"
if I search like that "select * from tbl_Users where username like 'e%' -> it gives two rows
1/ eswaran (english)
2/ éswaran. (french)
if I search like that "select * from tbl_Users where username like 'é%' -> it gives two rows
1/ eswaran (english)
2/ éswaran. (french)
but my requirement is when i search with french letter then the result should come the first row which is matching with french letter. It should should come like that.
1/ éswaran (french)
2/ enwaran (english)
but It comes like that
1/ eswaran
2/ éswaran.
when i search with english letter then the result should come the first row which is matching with english letter. It should should come like that.
1/ eswaran (english)
2/ éswaran (french)
How to query in sql server 2005?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

this seems to do what you want...i'm doing an order by on the username, and then the username with case sensitive collation:

the desision to order by the second as DESC or ASC, based on your search input i leave to you....

CREATE TABLE tbl_Users(uid int identity(1,1) primary key,username varchar(30) COLLATE sql_latin1_general_cp1_ci_ai null )

INSERT INTO tbl_Users

SELECT 'éswaran1' UNION ALL SELECT 'eswaran4' UNION ALL

SELECT 'éswaran2' UNION ALL SELECT 'eswaran3' UNION ALL

SELECT 'éswaran3' UNION ALL SELECT 'eswaran2' UNION ALL

SELECT 'éswaran4' UNION ALL SELECT 'eswaran5' UNION ALL

SELECT 'éswaran5' UNION ALL SELECT 'eswaran1'

select * from tbl_Users

select * from tbl_Users order by username,username collate sql_latin1_general_cp1_ci_as DESC

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!

eswaran_mca SSC Rookie Points: 45 More actions · Answer 2

hi,

i don't understand what you are trying to say...

you mean, i need to build two queries with union.

thanks

r.eswaran.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

No, he's saying you have to figure out what the user searched using, the english or the french letter and then make your sort ASC or DESC as appropriate. For example:

If ASCII([Search Character]) = 101 /* e */

Sort ASC

Else

Sort DESC

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

eswaran_mca SSC Rookie Points: 45 More actions · Answer 4

that english letter or french letter may come anywhere.

like i need to make search like that

select * from tbl_city where cityname like '%par%'

here, how can I find the occarence of french letter or english letter?

thanks

r. eswaran.

Lowell SSC Guru Points: 323506 More actions · Answer 5

basically you have to determine that if any high ascii character(>127) exists, you want to use DESC sort order on the example i provided, but if no high ascii exists, use ASC sort order;

i could not get an example to work in the ORDER BY, this may give someone some ideas;

i had to use an IF...ELSE to get the results for a pure SQL solution;

personally i would test the search term at the application level and call one of two search methods from the app, rather than try to do it at the SQL level. As Jack already pointed out, that is what i meant when i said "the decision to order by the second as DESC or ASC, based on your search input i leave to you...."

there's got to be an easier way to test for high ascii in TSQL, but this works and is fast.

--using my setup from above:

declare @searchterm varchar(30)

SET @searchterm = 'bob' --'éswaran1'

IF @searchterm LIKE '%[€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ]%'

--at least one high ascii character, use binary collation order

select *

from tbl_Users

--where username like '%' + @searchterm + '%'

order by

username,

username collate sql_latin1_general_cp1_ci_as DESC

ELSE

--no high ascii, use normal collation order