How to Query a Comma Separated Column for a Specific Value

Is it possible to query a comma separated column for a specific value?

You can, using LIKE. You don't want to match for partial values, so you'll have to include the commas in your search. That also means that you'll have to provide an extra comma to search for values at the beginning or end of your text:

select 
  * 
from
  YourTable 
where 
  ',' || CommaSeparatedValueColumn || ',' LIKE '%,SearchValue,%'

But this query will be slow, as will all queries using LIKE, especially with a leading wildcard.

And there's always a risk. If there are spaces around the values, or values can contain commas themselves in which case they are surrounded by quotes (like in csv files), this query won't work and you'll have to add even more logic, slowing down your query even more.

A better solution would be to add a child table for these categories. Or rather even a separate table for the catagories, and a table that cross links them to YourTable.

How to run select query on columns having comma separated values

Use the operator LIKE:

SELECT USER 
FROM DETAILS 
WHERE ',' || DEPARTMENT || ',' LIKE '%,' || 'Admin' || ',%'
  AND ',' || DEPARTMENT || ',' LIKE '%,' || 'Finance' || ',%'
  AND ',' || DEPARTMENT || ',' NOT LIKE '%,' || 'Accounts' || ',%';

Or the function INSTR():

SELECT USER 
FROM DETAILS 
WHERE INSTR(',' || DEPARTMENT || ',', ',' || 'Admin' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Finance' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Accounts' || ',') = 0;

This will work if there are no spaces after each comma in the column DEPARTMENT.

See the demo.

Select a Value from a table which contains Comma separated values in SQL Server

You can use PATINDEX() function
The PATINDEX() function returns the position of a pattern in a string.

If the pattern is not found, this function returns 0.

SELECT *, X.Order
FROM Table1 X
JOIN Table2 Y
    ON X.ID = Y.ID
WHERE
    X. Person = 'Person3' AND
    PATINDEX ('%,Res7,%',CONCAT(',',Y.Resource,',') )>0
  

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

how to select based on comma separated values in column

You can use the in-built find_in_set function.

find_in_set('s3',tags) > 0 and find_in_set('rds',tags) > 0

How to write redshift aws query to search for a value in comma delimited values

The simple way isn't always the best. There are a number of corner cases that can arise here (like are all country codes 2 letters). That said a LIKE clause would be simple:

select tb1.user_id, valid_country as country_code
from table1 tb1, table2 tb2 
where tb1.user_id=tb2.user_id 
  and tb1.country_code like '%'||tb2.valid_country||'%'

Or if we are to put this in modern SQL syntax:

select tb1.user_id, valid_country as country_code
from table1 tb1 join table2 tb2 
on tb1.user_id=tb2.user_id 
  and tb1.country_code like '%'||tb2.valid_country||'%'

comma separated values of specific column in table to list of integers in LINQ

string.Split and SelectMany are probably the pieces you're missing.

List<int> list1 =
     (
         from rar in unitOfWork.StudentRepository.GetAsQueryable()
         where rar.RequesterId == userId && rar.StatusId == 0
         select new { rar.RoleIds }
     )
    .AsEnumerable()
    .SelectMany(r => r.RoleIds.Split(','))
    .Select(int.Parse)
    .ToList();

How to get column values in one comma separated value

You tagged the question with both sql-server and plsql so I will provide answers for both SQL Server and Oracle.

In SQL Server you can use FOR XML PATH to concatenate multiple rows together:

select distinct t.[user],
  STUFF((SELECT distinct ', ' + t1.department
         from yourtable t1
         where t.[user] = t1.[user]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtable t;

See SQL Fiddle with Demo.

In Oracle 11g+ you can use LISTAGG:

select "User",
  listagg(department, ',') within group (order by "User") as departments
from yourtable
group by "User"

See SQL Fiddle with Demo

Prior to Oracle 11g, you could use the wm_concat function:

select "User",
  wm_concat(department) departments
from yourtable
group by "User"

---

Related Topics