SELECT FROM Table WHERE exact number not partial is in a string SQL

Question

I have a table that contains a bunch of numbers seperated by a comma.

I would like to retrieve rows from table where an exact number not a partial number is within the string.

EXAMPLE:

CREATE TABLE IF NOT EXISTS `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `uids` text NOT NULL,
  `islive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `teams` (`id`, `name`, `uids`, `islive`) VALUES
(1, 'Test Team', '1,2,8', 1),
(3, 'Test Team 2', '14,18,19', 1),
(4, 'Another Team', '1,8,20,23', 1);

I would like to search where 1 is within the string.

At present if I use Contains or LIKE it brings back all rows with 1, but 18, 19 etc is not 1 but does have 1 within it.

I have setup a sqlfiddle here

Do I need to do a regex?

Answer 1

You only need 1 condition:

select *
from teams
where concat(',', uids, ',') like '%,1,%'