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 : 1439 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	@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

@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

Latest Posts