SQL Function – Find a Value in a String of Values

In a table I have a field that you can use comma delimited value (example 1,2,3,22). When I tried to filtering use a “LIKE” statement on the value 2, I was being returned 2 and 22. ARG!

So what do you do? You create a simple function the returns a 1 or 0. 1 being successfully found and a 0 meaning failed to find. Seems simple enough? Well it was and the function can be easily tweaked to match the solution you need! Here is the function as I used it:

/****** Object:  UserDefinedFunction [dbo].[isFound]    Script Date: 05/03/2013 11:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Andrew Pallant
-- Create date: 03/05/2013
-- Description:	Find a value in a comma delimited string of values
-- =============================================
ALTER FUNCTION [dbo].[isFound] 
(
	@SearchString as varchar(200), 
	@SearchValue as varchar(10)
)
RETURNS bit
AS
BEGIN
	declare @rtn int
	set @rtn = 0

	-- Declare the return variable here
	set @SearchString=rtrim(ltrim(@SearchString)) 
	set @SearchValue=rtrim(ltrim(@SearchValue)) 
	set @SearchString = @SearchString + ','

	declare @i int   
	set @i=charindex(',',@SearchString)   
	declare @compValue varchar(10)
	while @i>=1 BEGIN
		set @compValue = left(@SearchString,@i-1)
		set @compValue=rtrim(ltrim(@compValue)) 
		set @SearchString=substring(@SearchString,@i+1,len(@SearchString)-@i)       
		set @SearchString=rtrim(ltrim(@SearchString)) 
		set @i=charindex(',',@SearchString)   
		if @compValue = @SearchValue BEGIN
			set @rtn = 1
		END
	END

	-- Return the result of the function
	RETURN @rtn

END

Now I can do a simple select statement and get the correct results.

select * [table1] where dbo.isFound([fieldname],2) = 1

I have also used it successfully in select with as a join.

SELECT * FROM [table1] t1 
   INNER JOIN [table2] t2 on .dbo.isFound(t1.[fiedldname],t2.[fieldname]) = 1