Using LIKE in MySQL SELECT fields

Question

I've been trying to select the status of doing a LIKE comparison:

SELECT (`my_column` LIKE '%thepattern'%) AS `did_match`

Thanks!

Edit: Just to clarify this was used so I could get this:

SELECT
     (CASE WHEN `title` LIKE '%keyword%' THEN 20 ELSE 0 END) +
     (CASE WHEN `desc` LIKE '%keyword%' THEN 10 ELSE 0 END)
     AS `match`
FROM myTable
ORDER BY `match`

Yeah, %keyword% and %thepattern% are the same in my example. Came back and posted the edit later on, using different working sample code. — Kristopher Ives
– Kristopher Ives, Commented Jun 10, 2010 at 2:38

OMG Ponies · Accepted Answer · 2010-06-08 23:45:35Z

2

You need to use:

SELECT t.my_column AS did_match
  FROM YOUR_TABLE t
 WHERE t.my_column LIKE '%thepattern%'

Replace YOUR_TABLE with the name of your table...

Without a FROM clause, MySQL (and SQL Server) will allow the query, but not against any data.

answered Jun 8, 2010 at 23:45

OMG Ponies

334k85 gold badges536 silver badges509 bronze badges

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

1 Comment

Imre L Over a year ago

This will work if he only wants columns that do match. You are right on the from <table>.

mechanical_meat · Accepted Answer · 2010-06-08 23:58:28Z

1

Not sure I understand the question, and it looks like OMG Ponies has figured it out better than I have, but in case this helps:

SELECT CASE WHEN `my_column` LIKE '%thepattern%' 
       THEN 'did_match' ELSE 'no_match' END CASE AS MYTEST
  FROM ...

Link to CASE statement documentation.

edited Jun 8, 2010 at 23:58

answered Jun 8, 2010 at 23:49

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

3 Comments

OMG Ponies Over a year ago

Adam: I'm not 100% that I understand what the OP intended. Hopefully they'll clarify

Kristopher Ives Over a year ago

Thanks guys, two potential solutions is better than one!

Kristopher Ives Over a year ago

Just wanted to add that this helped me a lot!

Collectives™ on Stack Overflow

Using LIKE in MySQL SELECT fields

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related