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:
id | name |
---|---|
1 | A |
2 | B |
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
ID | ProcessId | Process_Names |
---|---|---|
1 | 1,3,5 | Item 1,Item 3,Item 5 |
2 | 2,4 | Item 2,Item 4 |
3 | 1,2,3 | Item 1,Item 2,Item 3 |
4 | 1,5 | Item 1,Item 5 |
5 | (none) | |
6 | 3,4,12 | Item 3,Item 4,Item 12 |
Related Topics
How to Do File Upload Using Jquery Serialization
How to Submit Multiple PHP Forms While Each Form Has Its Own Submit Button
Multiple Column With Same Search String in Laravel
Join Category and Subcategory in Same Table
How to Install and Use PHP on Macos Via Brew
Assign Jquery Value to PHP Variable Inside Ajax Sussess
Cannot Simply Use Postgresql Table Name ("Relation Does Not Exist")
How to Use Bcrypt For Hashing Passwords in PHP
Reference - How to Handle Namespaces (Tags and Attributes With a Colon in Their Name) in Simplexml
Laravel How to Return Single Column Value Using Query Builder
How to Install Imagick/Imagemagick PHP Extension on Windows 7
Calculate Difference Between 2 Times in Hours in PHP
Redirect That Same Page from Where Id Is Coming
How to Block Direct Access to My JavaScript Files