What is the rule of order by with special character?

user_12267754 0
Viewed 72

enter image description here

I sort my data with select pk_customer_no from customer order by pk_customer_no The code with '-', didn't group together and sort by letter, It seems sql just ignore it and sort by the third letter.

How can I sort by the '-'?

sql-server sql
1 Answers

The '-' character is ignored in sorting.

You can use order by replace '-' with '0' (zero), if you want to put the words with '-' in front.

select t.pk_customer_no as rep from ( 
   values ('YH'), ('YHC'), ('Z-CH'), ('Z-CHE'), ('ZCM'), ('Z-CP'), ('Z1'), ('ZHT'), ('ZLA'), ('Z-JP'), ('ZLENO') 
) as t (pk_customer_no)
order by replace(t.pk_customer_no, '-', '0')

You can use order by replace '-' with 'Z' if you want to put the words with '-' at the end.

select t.pk_customer_no as rep from ( 
       values ('YH'), ('YHC'), ('Z-CH'), ('Z-CHE'), ('ZCM'), ('Z-CP'), ('Z1'), ('ZHT'), ('ZLA'), ('Z-JP'), ('ZLENO') 
) as t (pk_customer_no)
order by replace(t.pk_customer_no, '-', 'Z')
user_93914150
Related