Properly sorting products with names that start with quotes?

[Nexius]

Problem Solved: I spoke too soon. I guess the SQL "Replace" command can do the trick. For future reference, to get items that start with special characters to sort properly just use the syntax: ORDER BY REPLACE([field], '\"', ''); It seems to work.

 

Hi. I have a case where about half the items in a store begin with quotation marks. When sorting by product name, these always appear first (or last if in descending order) before the rest of the items are listed because SQL includes the quotation marks when ordering. For instance...

 

"Charlie Brown" video DVD

"Harry Potter" hardcover boxset

Elephant Plastic Figurine

Giant Inflatable Balloon

 

Instead, I would like the quotation marks to be ignored so that the order properly shows as:

 

"Charlie Brown" video DVD

Elephant Plastic Figurine

Giant Inflatable Balloon

"Harry Potter" hardcover boxset

 

I can't seem to find a way to do this in SQL while still allowing for products to be sorted ASC or DESC, and I haven't had success finding any information about it although I imagine I'm not the first to have such a dilemma. If I don't include an ORDER BY clause and instead just sort by default using a regular expression (i.e., '^\"*') then it works, but they aren't allowed to sort in descending order going this route and it also messes up other ordering methods such as by price.

 

Thanks.