Select query to retrieve records having ids as string in column like "1,5,6,8,10,15" [Resolved]

Posted by Kasani007 under Sql Server on 2/25/2017 | Points: 10 | Views : 295 | Status : [Member] | Replies : 2
I need a query which is having
student table
id studentname subjectids
1 Saketh 1,5,10
2 Vaishnav 6,10,15
3 Revanth 2,4,9

now i will pass subject id '10' and i need all the records related to the subject id 10

thanks in advance




Responses

Posted by: A2H on: 2/25/2017 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
You can use the Like Operator to get values from commaseparated string like below
Sample Code


SELECT 

*
FROM
[SubjectIdTable]
WHERE
[SubjectIds] LIKE '10,%' --Cover the scenario if 10 is first item in comma separted value
OR [SubjectIds] = '10' --Cover the scenario if 10 is only element with out any commas
OR [SubjectIds] LIKE '%,10,%' --Cover the scenario if 10 is middle item in comma separted value
OR [SubjectIds] LIKE '%,10' --Cover the scenario if 10 is Last item in comma separted value

Change the table names and column names as per your design
Sample Demo here : http://sqlfiddle.com/#!3/84c05/1



Thanks,
A2H
My Blog

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 2/26/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Kasani007 , another way of doing the same

DECLARE @T TABLE(ID INT IDENTITY,STUDENTNAME VARCHAR(50),SUBJECTIDS VARCHAR(50))

INSERT INTO @T VALUES('Saketh', '1,5,10'),('Vaishnav','6,10,15'),('Revanth','2,4,9')

SELECT *
FROM @T
WHERE CHARINDEX('10',SUBJECTIDS) > 0

/*
ID STUDENTNAME SUBJECTIDS
1 Saketh 1,5,10
2 Vaishnav 6,10,15

*/


--
Thanks & Regards,
RNA Team

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response