MYSQL using LIKE for a specific search

Question

I've come across a search situation that I am unsure how to approach. I am searching through a set of a column which is a string for example

Row 1: Column Content: 7;15;25
Row 2: Column Content: 5
Row 3: Column Content: 5;7

but I would like the row that has only 5

example: Select * from table where column like '%5%'

The problem with this case is that, the query will bring back all rows when I only needed Row 2 and Row 3

Is there anything that I could do with the query that will allow me to bring back the desired results.

I would truly appreciate the assistance. If there is any confusion with the question, I'll be happy clarify.

Mike Christensen · Accepted Answer · 2013-07-24 17:38:07Z

1

How about:

SELECT * FROM MyTable WHERE
CONTENT LIKE '5;%'      -- Starts with 5
OR CONTENT LIKE '%;5;%' -- 5 in the middle somewhere
OR CONTENT LIKE '%;5'   -- Ends with 5
OR CONTENT = '5';       -- 5 is the only item in the list

You could also simplify it as:

SELECT * FROM MyTable WHERE concat(';',CONTENT,';') LIKE '%;5;%';

Both of these solutions will probably result in fairly poor performance, especially if your table is huge. To solve that, I'd recommend normalizing this data and storing each selection as a row in another table.

answered Jul 24, 2013 at 17:38

Mike Christensen

91.8k52 gold badges223 silver badges349 bronze badges

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

1 Comment

Kai McKenzie Over a year ago

The first one worked best with the table i am using. I agree with you about the normalizing part. lol. Thank you so much. :)

Collectives™ on Stack Overflow

MYSQL using LIKE for a specific search

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related