MySql query challenge - return results with whitespace?

Question

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!!

And the answer is... SELECT * FROM reviews WHERE description REGEXP '[[:<:]]desc[[:>:]]' Fast and efficient. Thanks StackOverflow!! — barns
– barns, Commented Feb 11, 2013 at 12:41

Philipp · Accepted Answer · 2013-02-11 12:17:07Z

3

You can use regular expressions in SQL

SELECT * FROM `table` WHERE desc REGEXP '\bbike\b'

edited Feb 11, 2013 at 12:17

answered Feb 11, 2013 at 12:11

Philipp

15.7k4 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dipesh Parmar · Accepted Answer · 2013-02-11 12:30:32Z

1

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

edited Feb 11, 2013 at 12:30

answered Feb 11, 2013 at 12:15

Dipesh Parmar

27.3k8 gold badges65 silver badges92 bronze badges

2 Comments

barns Over a year ago

Thanks - Regex is the way to go.

barns Over a year ago

From here: tech-recipes.com/rx/484/… SELECT * FROM desc WHERE description REGEXP '[[:<:]]bike[[:>:]]' Works well and returns results in 0.0075 seconds on my WAMP server with 25k+ rows

Michal M · Accepted Answer · 2013-02-11 12:10:31Z

0

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 %';

answered Feb 11, 2013 at 12:10

Michal M

9,5218 gold badges51 silver badges64 bronze badges

6 Comments

Mark Baker Over a year ago

LIKE is case-sensitive: force case comparison to ensure that row 5 is returned as well

Michal M Over a year ago

@MarkBaker Interesting. According to MySQL Docs LIKE is case-insensitive.

Mark Baker Over a year ago

Depends on collation; but you most recommendations these days are to use utf-8, which is case-sensitive

Michal M Over a year ago

Agree, but default one isn't and in a lot of installations that's the case.

barns Over a year ago

I think UTF8_collation is case insensitive, but UFT8_bin is sensitive according to my tests. One thing though - desc LIKE 'bike %' will still match 'motorbike', for example.

|

Tapas Pal · Accepted Answer · 2013-02-11 12:14:03Z

0

Try this one, hope it'll help you

"SELECT * FROM `mytable` WHERE `desc` LIKE '% bike'

answered Feb 11, 2013 at 12:14

Tapas Pal

7,2578 gold badges45 silver badges90 bronze badges

1 Comment

andr Over a year ago

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.

Collectives™ on Stack Overflow

MySql query challenge - return results with whitespace?

4 Answers 4

Comments

2 Comments

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related