2

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 '-'?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Suspended
  • 169
  • 2
  • 11
  • 1
    This is a collation dependent behavior. Which dbms are you using? Which collation is used? – jarlh Jun 02 '22 at 13:28
  • 1
    Also, how come you can store NULL in a primary key?!? – jarlh Jun 02 '22 at 13:28
  • I'm use Chinese_Hong_Kong_Stroke_90_CS_AS_SC_UTF8 collation. And the null is for new row, not really exist. – Suspended Jun 02 '22 at 13:36
  • also my dbm is mssql – Suspended Jun 02 '22 at 13:39
  • you can try select pk_customer_no from customer order by replace(pk_customer_no,"-",""), just remove the '-' in order by – Znar Jun 02 '22 at 13:50
  • 1
    @Znar The question is ***to sort*** by '-' not ***not to sort***. – Shmiel Jun 02 '22 at 13:57
  • what jarlh said is correct, see [this qa](https://stackoverflow.com/a/361059) for example. you can try to `cast` it to use string comparison, but you may risk losing some non-ascii stuff if you have any. – Bagus Tesa Jun 02 '22 at 14:01
  • @jarlh: Since the record marker of the final record contains an asterisk (`*`), this is most likely not an existing record but a "template row" in the UI for creating new records. It's a convention used, for example, in MS Access and SQL Server Management Studio. – Heinzi Jun 02 '22 at 14:21
  • Yeah @Heinzi, That's correct. – shehanpathi Jun 02 '22 at 14:55

1 Answers1

3

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')
Benilson
  • 564
  • 2
  • 7