CODEWITHSUNDEEP
mysqlsqlsearch
Tono Nam
Tono Nam

Reputation: 36048

Create search query

I have a table named Customers with columns FirstName, LastName, Email

Let's pretend I have the customer: John | Williams | [email protected]

Now how do I have to create my query so that if I search for:

"williams"       =>  match
"will john"      =>  Match
"will john 55"   =>  NO match
"will 1234"      =>  match

my query right now looks like:

SELECT * FROM `Customers` WHERE `FirstName` LIKE _search_ OR `LastName` LIKE _search__

But if someone where to look for "will john" then my query will return no matches

Upvotes: 0

Views: 59

Answers (4)

muhmud
muhmud

Reputation: 4604

I think this works:

select * from Customers
where (_search_ regexp '^[^ ]+ [^ ]+$' or _search_ regexp '^[^ ]+$')
and (LastName like concat(substring_index(_search_, ' ',  1), '%'))
or FirstName like concat(substring_index(_search_, ' ',  -1), '%')));

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

Crudely...

SET @string = 'John|Williams|[email protected]';

SELECT IF(@string LIKE "%will%",IF(@string LIKE "%john%",IF(@string LIKE "%55%",1,0),0),0)x;
+---+
| x |
+---+
| 0 |
+---+

SELECT IF(@string LIKE "%will%",IF(@string LIKE "%john%",IF(@string LIKE "%12%",1,0),0),0)x;
+---+
| x |
+---+
| 1 |
+---+

Upvotes: 0

Nir Alfasi
Nir Alfasi

Reputation: 53525

Seems like you want to do something like that:

select * from Customers where 
(FirstName like concat('%','john', '%') and LastName like concat('%','smith', '%'))
or
(LastName like concat('%','john', '%') and FirstName like concat('%','smith', '%'))

The parts: john and smith (in the query) are the different parts of the search term which is exploded by spaces and modified to lowercase (you can do it either in the code or in the DB).

Link to Fiddle

Upvotes: 1

corvinusz
corvinusz

Reputation: 584

Dynamic sql can help you

 EXECUTE 'SELECT * FROM Customers WHERE FirstName LIKE ' ||
 _search_ || ' OR LastName LIKE ' || _search__ || ';';

"_ search _" should be converted to text (explicitly or not).

Of course, quotation waiting for your attention.

Upvotes: 0

Related Questions