SitePoint Sponsor |
|
User Tag List
Results 1 to 2 of 2
Thread: Mysql exact match on sub string
-
May 6, 2008, 04:44 #1
- Join Date
- May 2008
- Posts
- 1
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Mysql exact match on sub string
Hi,
Here is the table structure:
id (auto increment)
keywords (Text)
and values are:
1 google,microsoft
2 r,b
3 america,argentina
the keywords fieild has comma seperated values.
If i want to search for R then i it should only return one row. i.e. 2nd row.
but in my case, its returning all three rows because they have R in them.
My sql query is:
select * from table_name where keywords like 'r%'
i also tried:
select * from table_name where keywords like '%r'
and
select * from table_name where keywords like '%r%'
Can anybody tell me exact query that will return exact matched results.
-
May 6, 2008, 05:00 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
to answer your question...
... WHERE CONCAT(',',keywords,',') LIKE '%,r,%'
however, your table design means that you will always do a table scan (inefficient) because searching with a leading wildcard means the database cannot use an index on that column
it would be far better to redesign the table so that you are not storing a comma-delimited list in a single column
Bookmarks