Sql Get an Id from a Column Where Ids Separated by Commas

How can I pull a list of ID's from a SQL table as a comma-separated values string?

In addition to @OMG Ponies method, you could also try this COALESCE trick from:

Using COALESCE to Build Comma-Delimited Strings

declare @string nvarchar(255)

select @string = coalesce(@string + ', ', '') + cast(prodid as nvarchar(5))
from products

SQL: how to select rows where id is in comma separated string?

In MySQL you can use Find_in_set() to get your job done:

DB-Fiddle:

 create table test (id int, studentIDs  varchar(50));
insert into test values(1,'1,2,3');
create table student (id int, name varchar(50));
insert into student values(1,'A');
insert into student values(2,'B');

Query:

 select * from student where find_in_set(id,(select studentids from test))

Output:



















idname
1A
2B

get All records which has all IDs passed in comma separated string in sql server

If you want tutors with all subjects, you can use group by and having:

Select tutorid
from TutorSubject
where SubjectId in (92, 106, 1, 2, 91)
group by tutorid
having count(*) = 5; -- 5 is the length of the string

If you want to pass this in:

with s as (
select convert(int, value) as subjectid
from string_split(@s)
)
select tutorid
from tutorsubject ts join
s
on ts.subjectid = s.subjectid
group by tutorid
having count(*) = (select count(*) from s);

Select where an column with comma separated values contains an ID

What about:

SELECT * FROM tbl_post WHERE tbl_post.tags LIKE '%15%';

OR

SELECT * FROM tbl_post WHERE Contains(tbl_post.tags, '15');

As per your comment, you could try this

DECLARE @id INT = 15
DECLARE @stringId VARCHAR(50) = CAST(@id AS VARCHAR(50))

SELECT *
FROM tbl_post
WHERE tbl_post.tags = @stringId -- When there is only 1 id in the table
OR tbl_post.tags LIKE @stringId + ',%' -- When the id is the first one
OR tbl_post.tags LIKE '%,' + @stringId + ',%' -- When the id is in the middle
OR tbl_post.tags LIKE '%,' + @stringId -- When the id is at the end

Referenced from this SO post

SQL select from another table values comma separated where ids are contained in a string

SELECT table2.ID, 
table2.Name,
GROUP_CONCAT(table1.Name) TypeNames
FROM table1
JOIN table2 ON LOCATE(table1.Type, table2.Type)
GROUP BY table2.ID,
table2.Name;

fiddle

filter ids by comma separated nvchar ids

You may use this. You need to look from other side in this problem. I found it very interesting.

Actually you have a list of id's as base, and you want to exclude id from the string which are not in some table. So first we need to find the list of id's, after that we'll exclude them from the id's of table to get our desired result. At the end you may use stuff or string_agg to convert your final result into , separated string.

select Value from (
select value from string_split('1,2,3',',')) as t
where t.value not in (select id from demotable)

You may check this link for working fiddle.FIDDLE.

In MSSQL filter rows based on an ID exists in a column as comma separated string

You should fix the data structure. Storing numeric ids in a comma-delimited list is a bad, bad, bad idea:

  • SQL Server doesn't have the best string manipulation functions.
  • Storing numberings as character strings is a bad idea.
  • Having undeclared foreign key relationships is a bad idea.
  • The resulting queries cannot make use of indexes.

While you are exploring what a junction table is so you can fix the problem with the data structure, you can use a query such as this:

where testid = 3 or
',' + ConnectedTestID + ',' like '%,3,%'

How to get values from table using multiple ID's in single column in SQL?

Two tricks are needed. One is joining based on a comma-separated list of IDs. That can easily be done poorly resulting in unwanted matches such as 1 and 2 matching 12. The article Stephen Jennings referenced has some good reliable solutions.

The second is concatenating a collection of results into a single string. For recent versions of SQL Server, the STRING_AGG is the preferred solution. For older versions (such as 2014) the most common method is the "FOR XML" trick.

I've combined the two techniques below.

DECLARE @Tbl_Process TABLE(ID INT, process_Name VARCHAR(100))
INSERT @Tbl_Process
VALUES (1, 'Item 1'), (2, 'Item 2'), (3, 'Item 3'), (4, 'Item 4'), (5, 'Item 5'), (12, 'Item 12')

DECLARE @Tbl_av TABLE(ID INT, ProcessId VARCHAR(100))
INSERT @Tbl_av
VALUES (1, '1,3,5'), (2, '2,4'), (3, '1,2,3'), (4, '1,5'), (5, ''), (6, '3,4,12')

SELECT AV.*, PL.*
FROM @Tbl_av AV
CROSS APPLY (
SELECT ISNULL(STUFF(
(
SELECT ',' + P.process_Name
FROM @Tbl_Process P
--(bad) WHERE CHARINDEX(CONVERT(VARCHAR, P.ID), AV.ProcessId) > 0
WHERE ',' + AV.ProcessId + ',' LIKE '%,' + CONVERT(VARCHAR, P.ID) + ',%'
ORDER BY P.ID -- alternately ORDER BY P.process_Name
FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)')
, 1, 1, '')
, '(none)')
AS Process_Names
) PL

Results



Leave a reply



Submit