[SOLVED] Using REPLACE to sort alphabetically ignoring punctuation?

[blakekr]

Someone very good at mysql helped me create an ORDER BY statement that would allow my program to display item names alphabetically without putting items in quotes at the beginning. Works a charm, except I can't figure out how to craft a REPLACE statement that ignores more than one type of punctuation ... e.g. I need to ignore both double quotes and single quotes at least.

 

Here is my original statement -- works great:

 

        if ($SortBy == 1) { // Either explicit or default set above
             $orderBy = "category , REPLACE(`prod_name`,'\"', '') ";

 

 

Here is my attempt to ignore both double and single quotes, doesn't work as expected:

 

        if ($SortBy == 1) { // Either explicit or default set above
             $orderBy = "category , REPLACE(REPLACE('prod_name','\\\"',''),'\'','') ";

 

Any advice appreciated!

 

Thanks,

Blake

[mezise]

Hi,

in inner REPLACE you are searching for \" instead of ".

This code should work:

if ($SortBy == 1) { // Either explicit or default set above
             $orderBy = "category , REPLACE(REPLACE('prod_name','"',''),'\'','') ";

[blakekr]

Thanks so much, this was tremendously helpful!