/ ************************************** FN_FINDSTRING ** Find from the target string String **Xspf.cn@gmail.com******************************************* ****** /
- @ strDest: Target string ',' Separation - @ strpattern: Match strings (',' separation)
- Return: All matching items found
If EXISTS (SELECT [Name] from sysobjects where [name] = 'fn_findstring' and [type] = 'tf') Begin Drop Function FN_FINDSTRINGEND
Go
Create Function Fn_FindString (@StrDest VARCHAR (255), @ StrPattern Varchar (255)) Returns @Result Table (Substr Varchar (31)) asbegin declare @startpos int, @commapos int
Declare @Destsubstrtable table (Substr Varchar (31)) Declare @Patternsubstable Table (Substr Varchar (31))
/ * You need to match the string to split into a child string * / set @startpos = 0 set @commapos = charIndex (',', @ strpattern, @ startpos)
While (@commapos! = 0) Begin Insert Into @patternsubstable Values (Ltrim (@ strpattern, @ startpos, @ commapos- @ startpos))))))
Set @startpos = @commapos 1 set @commapos = charindex (',', @ strpattern, @ startpos) end
INSERT INTO @Patternsubstrtable Values (Ltrim (@ StrPattern, @ StartPos, Len (@strpattern) 1- @ StartPOS)))))))
/ * Target stroke split into child string * / set @startpos = 0 set @commapos = charIndex (',', @ strdest, @ startpos)
While (@commapos! = 0) Begin Insert INTO @Destsubstrtable Values (Ltrim (@ strapos- @ startpos)))))))))
Set @startpos = @commapos 1 set @commapos = charindex (',', @ strdest, @ startpos) end
INSERT INTO @Destsubstable Values (@Strim (@STRDEST, @ StartPos, Len (@StrDest) 1- @ StartPOS))))) / * Results All Matches in Target Strings * / Insert Into @Result Select LHS. * from @Destsubstrtable as lh join @Patternsubstrtable as rhs on lhs.substr = rhs.substr
Return End
Go
SELECT * from Fn_Findstring ('10, 20, 25, 26, 28, 30, 32 ',' 12, 1, 20, 30 ')
Go
