[MySQL] Sorting database results
Moderator: General Moderators
- ILoveJackDaniels
- Forum Commoner
- Posts: 43
- Joined: Mon May 20, 2002 8:18 am
- Location: Brighton, UK
[MySQL] Sorting database results
I have written a small search script in PHP, to return results from a MySQL database, which are listed alphabetically by the database automatically using the "ORDER BY" thingummy.
The problem is that this has been written for a client of mine. He has started entering data into the database and is using square brackets ( [ ] ) in places at the start of the field which is used to sort the results of the search, so although almost everything is ordered alphabetically, the fields using square brackets at the start are placed at the very end of any search.
Is it possible when grabbing results from a MySQL database to have the database ignore certain characters for the purposes of the ordering of the results? i.e. the results would be ordered alphabetically whether or not the field started with a square bracket....
The problem is that this has been written for a client of mine. He has started entering data into the database and is using square brackets ( [ ] ) in places at the start of the field which is used to sort the results of the search, so although almost everything is ordered alphabetically, the fields using square brackets at the start are placed at the very end of any search.
Is it possible when grabbing results from a MySQL database to have the database ignore certain characters for the purposes of the ordering of the results? i.e. the results would be ordered alphabetically whether or not the field started with a square bracket....
Re: [MySQL] Sorting database results
or, you could update all of the entries that use []'s (that is, remost the []'s).
-c.w.collins
-c.w.collins
- ILoveJackDaniels
- Forum Commoner
- Posts: 43
- Joined: Mon May 20, 2002 8:18 am
- Location: Brighton, UK
Okay, this might take a little work, but you can try something like this:
Code: Select all
SELECT SUBSTR(bracketed_field,1,LENGTH(bracketed_field)) as good_field FROM table ORDER BY good_field- ILoveJackDaniels
- Forum Commoner
- Posts: 43
- Joined: Mon May 20, 2002 8:18 am
- Location: Brighton, UK
Problem with replacing the square brackets is that the client actually wants them there...
Could do something along those lines, Jason. Maybe I could run a script on the thing to duplicate the field for the purposes of searching but not display, removing the square brackets and change the data entry script to add the duplicate field in as well. All sounds like much fun .... no really....
Could do something along those lines, Jason. Maybe I could run a script on the thing to duplicate the field for the purposes of searching but not display, removing the square brackets and change the data entry script to add the duplicate field in as well. All sounds like much fun .... no really....
- ILoveJackDaniels
- Forum Commoner
- Posts: 43
- Joined: Mon May 20, 2002 8:18 am
- Location: Brighton, UK
I hope this might be a slightly better version for your answer :
Code: Select all
SELECT yourfield, substring(yourfield from 2) as dummy FROM yourtable order by dummy