SQL split by comma

SQL Split Comma:-
     In the below example we are going to see the function for split string by (,) comma and separate the words or number and apply to the where condition using 'in' method and select the values.

Select * from employee;

EmpID	First Name	Last Name	JobID	Place
1	Rajandran	Ram	103	Madurai
2	Belly	Kumar	106	Chennai
3	Ramanathan	Jino	99	Mulagumoodu
4	Anitha	Rani	99	ManaliVilai
5	Rajeshwari	Sam	115	Nagercoil
6	Ajitha	Kumari	23	Marthandam
7	Anusha	Prithy	222	Koyambuthoor

Split Function:-

CREATE FUNCTION [dbo].[SplitString1](@StringValue varchar(2000), @Delimiter char(1))      

returns @resulttable TABLE (words varchar(2000))      

as      

begin      

      declare @index int      

      declare @sliceOfStringValue varchar(2000)      

     

      set @index = 1      

            if len(@StringValue)<1 or @StringValue is null  return      

     

      while @index!= 0      

      begin      

            set @index = charindex(@Delimiter,@StringValue)      

            if @index!=0      

                  set @sliceOfStringValue = left(@StringValue,@index - 1)      

            else      

                  set @sliceOfStringValue = @StringValue      

             

            if(len(@sliceOfStringValue)>0)  

                  insert into @resulttable(words) values(@sliceOfStringValue)      

   

            set @StringValue = right(@StringValue,len(@StringValue) - @index)      

            if len(@StringValue) = 0 break      

      end  

return      

end

String value is '1,3,4,6'

Example to access the above function:-

select * from Emp_tb_eob_Employee where EmployeeID in(select * from [dbo].[SplitString1]('1,2,3,4',','))

Output:-

EmpID	First Name	Last Name	JobID	Place
1	Rajandran	Ram	103	Madurai
3	Ramanathan	Jino	99	Mulagumoodu
4	Anitha	Rani	99	ManaliVilai
6	Ajitha	Kumari	23	Marthandam

Screenshot:-