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

Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http://www.unlatched.com/#sthash.8DiTkpKy.dpuf

Posted in SQL Tagged with:
2 comments on “SQL Function – Find a Value in a String of Values
  1. Dhanasekaran R says:

    Hi Friend,

    It was really a great help..saved my day.
    Thanks a lot.

    Regards,
    Dhana.

Leave a Reply

Your email address will not be published. Required fields are marked *

*