Monday, February 16, 2009

SQL SERVER: Function To Search Coma or Delimiter separated value in a column

We sometimes stores Coma or other delimiter separated values in a column, to search a particular value in that column, the following function will be helpful.



--30-jan-2009 Priyan R
CREATE FUNCTION [dbo].[IsExistInString]
(
@Data VARCHAR(MAX),
@Delim VARCHAR(100),
@ValueToFind VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
DECLARE @pos1 INT
DECLARE @pos2 INT
DECLARE @tbl_Split_Data TABLE
(
Data VARCHAR(MAX)
)
SET @pos1=1
IF(CHARINDEX(@Delim,@Data,1)=0)
BEGIN
INSERT INTO @tbl_Split_Data VALUES(@Data)
END
ELSE
BEGIN
WHILE (@pos1<>0)
BEGIN
SET @pos2=CHARINDEX(@Delim,@Data,@pos1)
IF(@pos2=0)
BEGIN
INSERT INTO @tbl_Split_Data VALUES(SUBSTRING (@Data,@pos1,LEN(@Data)))
END
ELSE
BEGIN
INSERT INTO @tbl_Split_Data VALUES(SUBSTRING (@Data,@Pos1,@Pos2-@Pos1))
END
IF(@pos2<>0) SET @pos2=@pos2+LEN(@Delim)
SET @pos1=@pos2
END
END
SELECT @POS1=COUNT(*) FROM @tbl_Split_Data WHERE Data=@ValueToFind
IF(@POS1<>0)
BEGIN
RETURN 1
END
RETURN 0
END

go
--eg
SELECT dbo.IsExistInString('one|$|two|$|three','|$|','one')

No comments: