CODEWITHSUNDEEP
phpmysql
user961882
user961882

Reputation: 237

Ignoring characters in field string in MySQL

I'm pulling a list of names from a database;

SELECT * FROM people ORDER BY name ASC

However, some of the names are like [Mod]John, and these are all at the end of the list instead of in alphabetical order, because of starting with [.

Is there a simple way to ignore the [mod] part when sorting my names, but still pull the entry? So I still have John in my list?

Do I need to sort them using PHP after pulling?

Upvotes: 0

Views: 121

Answers (6)

andre
andre

Reputation: 194

Use:

SELECT * FROM people ORDER by REPLACE(name, '[mod]', '');

(This works in MySQL.)

Upvotes: 0

symcbean
symcbean

Reputation: 48357

You omitted to mention what collation you are currently using. You also forgot to explain how you do want it sorted - do you want the results ordered as if the '[mod]' was not there? Or based on order of the characters in the ASCII table?

The '[' character (0x5B) comes before the lower case letters in ASCII, but after the upper case letters. So to sort based on the ASCII character code:

  1. Use a case-sensitive sort

  2. Sort by `lower(name) ASC

  3. ORDER BY name LIKE '[mod]%' DESC, name ASC

If you just want to ignore it for sorting purposes:

ORDER BY REPLACE(name, '[mod]','') ASC

Upvotes: 0

Zak
Zak

Reputation: 7515

You can do it this way -- using NOT LIKE -- if you are attempting to exclude the names starting with '[mod]':

SELECT * FROM people WHERE name NOT LIKE '[mod]%';

Upvotes: 0

Shaun Hare
Shaun Hare

Reputation: 3871

Use (please check syntax):

SELECT *, replace(name, '[mod]', '') as sortname FROM people order by sortname ASC

But essentially use the replace function.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108380

I would use the MySQL SUBSTRING_INDEX function. e.g.

 ORDER BY SUBSTRING_INDEX(name,']',-1)

That's going to start at the end of the string, and look backwards until it finds the delimiter character, in this example, a right square bracket. When it finds the delimiter, it returns everything so far, up to (or back to) the delimiter. If the delimiter isn't found, then it returns the whole string.

We can test the expression in the SELECT list, to better observe the behavior, before including it in the ORDER BY.

As a demonstration:

SELECT t.name
     , SUBSTRING_INDEX(t.name,']',-1) AS x_mod
  FROM ( SELECT '[mod]john' AS name
         UNION ALL SELECT 'james'
         UNION ALL SELECT 'jude'
       ) t 
 ORDER BY 2

returns

name       x_mod   
---------  --------
james      james   
[mod]john  john    
jude       jude

Upvotes: 0

Walker Boh
Walker Boh

Reputation: 770

Replace the [Mod] tag with an empty string:

ORDER BY REPLACE(name, '[Mod]', '')

Upvotes: 1

Related Questions