[SOLVED] Help with LIKE statement...what am I not seeing?

[JD*]

Hey all,

 

Been bashing my head against this for a bit...I'm trying to grab users from a particular building using a LIKE statement, but I think I need some outside perspective.

 

For my site, users are assigned to building in a column like:

 

User Name	Building
Joe Smith	1,2
Jane Smith	2,4
Don Ho	12,3

 

What I'm looking to do is pull all users where the building, lets say, is 2. Currently, doing this:

 

SELECT * FROM table WHERE building LIKE "%2%"

 

Is going to return both Joe Smith, Jane Smith AND Don Ho, when I only want Joe and Jane. If I add in a "%,2,%", I'm not going to get Jane Smith because there is no comma in front of her building number.

 

Now, I know I can put a 0 in front of my single digit numbers, but before I do so, I wanted to know if I'm missing anything. This all ties into a function where I pass the current building I'm looking for, so it needs to be fairly flexible.

 

Thanks!

[fenway]

You don't want to store multiple values in a single field that ... use a separate table.

[JD*]

I was hoping for a better answer, but that's cool. I actually do have a table that records each of these in a separate row, but was hoping to migrate it over to this newer setup...

 

Thanks for the help

[fenway]

If you're stuck with this data, use FIND_IN_SET() -- as long as they're comma-separated, it will work.