tags:

views:

95

answers:

5

Usually, I use

SELECT * FROM tableName WHERE fieldName LIKE '%string%'

to find every row in tableName where the value of fieldName contains the string 'string'. How can I find every row in tableName where the value of fieldName is contained in the string 'string'?

For example, in the following table

names

FirstName | LastName
Bob       | Dylan
Bob       | Marley

I would like to use the string "Bob Marley" in order to find [Bob | Marley] and [Bob | Dylan], so I would say: Find all rows in names where the value of FirstName is contained in "Bob Marley"

I thought of this:

SELECT * FROM tableName WHERE 'string' LIKE %fieldName%

but it doesn't work.

+1  A: 

SELECT * FROM tablename WHERE 'string' like '%' + fieldname + '%'

Mayo
A: 
SELECT  *
FROM    (
        SELECT  'Bob' AS name, 'Marley' AS lastname
        UNION ALL
        SELECT  'Bob' AS name, 'Dylan' AS lastname
        ) q
WHERE   'Bob Marley' LIKE CONCAT('%', name, '%')
Quassnoi
+2  A: 

Try this:

Select * From tableName where 'string' Like '%' + fieldName + '%'
Chris Nielsen
A: 

Try this:

SELECT *
  FROM tableName
 WHERE LOCATE(fieldName, 'string') > 0;
Ken Keenan
Thank you, your solutions works fine. Still, I will use my own solution:Select * From utilisateur where 'Shawn Freyssonnet-Inder' Like CONCAT('%', Prenom, '%')because I find it more semantically correct.. I'm not trying to locate the string, but rather testing if it is present. The only problem is I cannot yet select my own solution as my accepted solution.. so you get the prize money for now
Shawn
"for now", that is lame
KM
I wonder why you say lame... Anyways, lame or not, the answer that I, the asker, think is the best is my own (strongly inspired by two other answers which I mention). If however your answer is better, it should get voted up to second place. But for that, perhaps you should defend your answer and show me how it is better than mine. If you manage to convinve me, I will be happy to change my vote. Until then, sorry for depriving you of 15 precious reputation points.
Shawn
+1  A: 

Thanks to Chris Nielsen and mmayo, I found this working solution:

Select * From utilisateur where 'Shawn Freyssonnet-Inder' Like CONCAT('%', Prenom, '%')
Shawn