I have a MySql database with some rows as follows:
ID DESC
1 This is my bike
2 Motorbikes are great
3 All bikers should wear helmets
4 A bike is great for exercise
5 A. Top. Bike.
What I want to do is return the rows with whitespace surrounding the search term, OR the term being at the end or beginning of the description.
For example,
"SELECT * FROM `mytable` WHERE `desc` LIKE '%bike%'"
Will return all rows. But,
"SELECT * FROM `mytable` WHERE `desc` LIKE '% bike %'
Will only return row 4.
What I really want is a reliable way to return rows 1, 4 and 5, i.e. where the search term is sorrounded with anything BUT chars A-z, 0-9. Any ideas? Is this even possible with MySql?
Thanks!!
You should start reading about MySql RegEx.
Sample Code.
SELECT * FROM table WHERE field_name REGEXP PATTERN;
More Specific
details Table
ID NAME 1 Dipesh 2 Dip 3 Dipe 4 DiDi 5 Di
SELECT * FROM details WHERE NAME REGEXP '^Di$';Result
NAME -> Di
SELECT * FROM details WHERE NAME REGEXP 'Di$';Result
NAME -> DiDi , Di
SELECT * FROM details WHERE NAME REGEXP '^Di';Result
NAME -> Dip, DiDi, Di
You need to specify the additional conditions in the query:
SELECT *
FROM `mytable`
WHERE
`desc` LIKE '% bike %' OR
`desc` LIKE '% bike' OR
`desc` LIKE 'bike %';
You can use regular expressions in SQL
SELECT * FROM `table` WHERE desc REGEXP '\bbike\b'
Try this one, hope it'll help you
"SELECT * FROM `mytable` WHERE `desc` LIKE '% bike'