Rhino,
String comparisons are case insensitive in mysql by default.
mysql> SELECT 'bang' LIKE '%ang%', 'Angel' LIKE '%ang%'; +---------------------+----------------------+ | 'bang' LIKE '%ang%' | 'Angel' LIKE '%ang%' | +---------------------+----------------------+ | 1 | 1 | +---------------------+----------------------+ 1 row in set (0.06 sec)
Rhino wrote:
I don't think you are putting this question very well. You are not really asking about sorting, which is accomplished (primarily) via the ORDER BY clause; you are really asking about searching for rows that meet a certain criteria, which is accomplished (primarily) via the WHERE clause.
If you want to find all the rows that contain 'Ang', you can do this easily via the LIKE keyword. You will want something like this:
select name from mytable where name like '%ang%'
That means that you want to return the name of everyone who has the letters 'ang', in that exact order, with no intervening spaces, anywhere in the name; there may or may not be letters before the 'a' and there may or may not be letters after the 'g'.
However, if your data is mixed case, you will want to modify the query as follows:
select name from mystable where name like '%ang% or name like '%Ang%'
This is because the first query I gave you will not find 'John Ang', 'Anglosaxon', or 'Ang Fernandez'. The first query is looking only for lower case 'ang' so you will also need to search on '%Ang%' to get 'John Ang', 'Anglosaxon' and 'Ang Fernandez'. If your data is all strictly uppercase, you could get away with this:
select name from mytable where name like '%ANG%'
Rhino
----- Original Message ----- From: "Erik Bukakis" <[EMAIL PROTECTED]>
To: "MySQL Discussion List" <mysql@lists.mysql.com>
Sent: Monday, May 09, 2005 3:10 PM
Subject: Sorting by relevance?
I just learned a lot stuff at http://dev.mysql.com/doc/mysql/en/sorting-rows.html including sorting by number-to-text, text-to-number, names, specific values, etc.
However, the document didn't mention on how to sort by relevance.
For instance, someone search for "Ang": COLUMN NAME: name COLUMN TYPE: varchar (255) DATA: John Ang La Chi Zoygote Anglosaxon Marco Polo Ang Fernandez John Pang Cuyi
Query result should be: Ang Fernandez John Ang Anglosaxon John Pang Cuyi
Is it possible to sort a column this way?
^_^ Thanks in advance. ^_^
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
