CODEWITHSUNDEEP
mysqlsqlregexsql-likecontains
Robert
Robert

Reputation: 925

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

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?

Upvotes: 4

Views: 1276

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You SQL Fiddle is using MySQL and your syntax is consistent with MySQL. There is a built-in function to use:

select t.*
from teams t
where find_in_set(1, uids) > 0;

Having said that, FIX YOUR DATA MODEL SO YOU ARE NOT STORING LISTS IN A SINGLE COLUMN. Sorry that came out so loudly, it is just an important principle of database design.

You should have a table called teamUsers with one row per team and per user on that team. There are numerous reasons why your method of storing the data is bad:

  • Numbers should be stored as numbers, not strings.
  • Columns should contain a single value.
  • Foreign key relationships should be properly declared.
  • SQL (in general) has lousy string handling functions.
  • The resulting queries cannot be optimized.
  • Simple things like listing the uids in order or removing duplicate are unnecessarily hard.

Upvotes: 0

Mike M.
Mike M.

Reputation: 12511

You didn't specify which version of SQL Server you're using, but if you're using 2016+ you have access to the STRING_SPLIT function which you can use in this case. Here is an example:

CREATE TABLE #T
     (
        id int,
        string varchar(20)
     )

     INSERT INTO #T
     SELECT 1, '1,2,8' UNION
     SELECT 2, '14,18,19' UNION
     SELECT 3, '1,8,20,23' 


     SELECT * FROM #T
     CROSS APPLY string_split(string, ',')
     WHERE value = 1

Upvotes: 1

dfundako
dfundako

Reputation: 8314

You could probably catch them all with a OR

SELECT ...

WHERE uids LIKE '1,%'
OR  uids LIKE '%,1'
OR uids LIKE '%, 1'
OR uids LIKE '%,1,%'
OR uids = '1'

Upvotes: 3

The Impaler
The Impaler

Reputation: 48780

I would search for all four possible locations of the ID you are searching for:

  • As the only element of the list.
  • As the first element of the list.
  • As the last element of the list.
  • As an inner element of the list.

The query would look like:

select *
from teams
where uids = '1' -- only
   or uids like '1,%' -- first
   or uids like '%,1' -- last
   or uids like '%,1,%' -- inner

Upvotes: 3

forpas
forpas

Reputation: 164099

You only need 1 condition:

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

Upvotes: 5

Related Questions