SQL order by products that punctuation mark at the end

Question

We got a database of products, we need to order them by name (A-Z) but if the product name begins with a punctuation mark (e.g. ¡A tapear! or ¡Adelgaza!) it should appear near the end and not at the beginning. Is there any way to issue such a query?

score 3 · Accepted Answer · answered Sep 18 '12 at 21:01

3

If you simply want to push the A-Z's to the front, and let everything else sort naturally, then use this:

ORDER BY case when PRODUCT_NAME like '[a-Z]%' then 1 else 2 end, PRODUCT_NAME

answered Sep 18 '12 at 21:01

RichardTheKiwi

105,798
26
196
262

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

If you can extract a list of the punctuation marks you need to ignore, you can actually change the values MySQL sorts by with a statement like:

ORDER BY REPLACE(product_name, "¡", "")

You can nest several REPLACE() as many times you need.

This has been asked before here.

edited May 23 '17 at 11:43

Community

1
1

answered Sep 18 '12 at 20:49

Tibi Neagu

391
1
10

Ok, I don`t want to ignore them, but i guess i can replace those symbols for a value greater than Z – zad Sep 18 '12 at 20:52
Yes, using a function on your data should cause MySQL to ignore your indexes. If possible, precompute, store, and index the "clean" value (or maybe just the first 10 characters, etc). – Alain Collins Sep 18 '12 at 21:19

SQL order by products that punctuation mark at the end

2 Answers2