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!<\/p>\n
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:
\n<\/p>\n
\/****** Object: UserDefinedFunction [dbo].[isFound] Script Date: 05\/03\/2013 11:54:23 ******\/\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n-- =============================================\r\n-- Author:\t\tAndrew Pallant\r\n-- Create date: 03\/05\/2013\r\n-- Description:\tFind a value in a comma delimited string of values\r\n-- =============================================\r\nALTER FUNCTION [dbo].[isFound] \r\n(\r\n\t@SearchString as varchar(200), \r\n\t@SearchValue as varchar(10)\r\n)\r\nRETURNS bit\r\nAS\r\nBEGIN\r\n\tdeclare @rtn int\r\n\tset @rtn = 0\r\n\r\n\t-- Declare the return variable here\r\n\tset @SearchString=rtrim(ltrim(@SearchString)) \r\n\tset @SearchValue=rtrim(ltrim(@SearchValue)) \r\n\tset @SearchString = @SearchString + ','\r\n\r\n\tdeclare @i int \r\n\tset @i=charindex(',',@SearchString) \r\n\tdeclare @compValue varchar(10)\r\n\twhile @i>=1 BEGIN\r\n\t\tset @compValue = left(@SearchString,@i-1)\r\n\t\tset @compValue=rtrim(ltrim(@compValue)) \r\n\t\tset @SearchString=substring(@SearchString,@i+1,len(@SearchString)-@i) \r\n\t\tset @SearchString=rtrim(ltrim(@SearchString)) \r\n\t\tset @i=charindex(',',@SearchString) \r\n\t\tif @compValue = @SearchValue BEGIN\r\n\t\t\tset @rtn = 1\r\n\t\tEND\r\n\tEND\r\n\r\n\t-- Return the result of the function\r\n\tRETURN @rtn\r\n\r\nEND<\/pre>\nNow I can do a simple select statement and get the correct results.<\/p>\n
select * [table1] where dbo.isFound([fieldname],2) = 1<\/pre>\nI have also used it successfully in select with as a join.<\/p>\n
SELECT * FROM [table1] t1 \r\n INNER JOIN [table2] t2 on .dbo.isFound(t1.[fiedldname],t2.[fieldname]) = 1<\/pre>\n","protected":false},"excerpt":{"rendered":"