SQLTeam.com | Weblogs | Forums

Hyphen is ignored in sql server order by desc


#1

i am facing a strange issue while applying sorting on columns of datatype nvarchar and the data is in Russian language.
Many columns contain hyphen ( - ) at start,end and middle positions and while applying sorting the hyphen symbol or character is neglected and the sorting is performed based on the character next to hyphen.

I have tried with collation concept used sql_Latin1_General_CP1_CI_AS to sort data and its working fine on columns which doesn't contain hyphen but fails on which contains hyphen.

Any suggestions?
Thanks in advance.


#2

Have you tried ORDER BY CASE, something like:

select * from #testorder order by case when id = '-' then 0 else 1 end, id desc ]


#3

Is there any chance that SQL is makign an IMPLICIT conversion to Numeric/Integer of some sort?


#4

Kristen:
As there are no numeric or integer values in column so there is no scope for conversion


#5

Thank you all for your valuable answers and suggestions.

I dont want to replace the hyphen and then apply sort i just wanted that hyphen to be considered as a character like other special characters like ' * , . % $ # ' while sorting.

The issue was with the collation which i used to sort concatenated column values.
Using ( Latin1_General_BIN ) resolved my issue. :grinning:


#6

That will sort case INsensitive (Capital Letters and Lower Case Letters separately - so you will get "A" - "Z" first followed by "a" - "z", separately) is that OK?


#7

Kristen:
i was wrong about Latin1_General_BIN collation.
Its not ok with me.
You were right it sorts all uppercase and lowercase separately.It seems problem not yet resolved any other suggestions?


#8

Actually we have a application where we show the address details like client name,address Line,zip code, state and city concatenated together as one column.
There are cases where client name is empty or address line is empty but when we concatenate the details we don't consider the empty values we just ignore them while concatenation.

So when end-user tries to sort address details ,details should be sorted correctly.But if i sort the client name,address Line etc individually and then apply concatenation my result will not be as expected.

For example
client name || address Line || City || zip code
Ab || Alabama,1-a || Alabama || 1234
Ab || empty || Georgia || 1234

when concatenated details will look like
Ab;Alabama,1-a;Alabama;1234
Ab;Georgia;1234

so if now sort (ASC) the details end-user expects the results to be as follows
Ab;Alabama,1-a;Alabama;1234
Ab;Georgia;1234

but i sort columns individually and then concatenate the results would be
Ab;Georgia;1234
Ab;Alabama,1-a;Alabama;1234
which he thinks to be wrong as Alabama should come before georgia

so i have to concatenate the values and apply sort as i am showing the concatenated values as address details.


#9

I can see how that makes sense for the sort order to need to be by the concatenated fields.

In a sense I'm relieved that the BINARY collation didn't solve the problem because when I read your earlier reply I was wondering how it was possible that that had fixed the problem!

Sorry, I still have no idea why the leading hyphen is being lost. Are you sure it is a hyphen and not some extended character?


#10

Kristen:
It is a hyphen. :disappointed:


#11

sz1:
Thank you for the reply.
Didn't worked out.