日期:2014-05-18 浏览次数:20465 次
[code=SQL][code=SQL]现在用存储过程参数带过来的字符为"csdn是最大的程序员大本营" 怎样把它们一个一个拆分开来成这样? select * from csdn where name like '%csdn% 'or name like '%是% 'or name like '%最% 'or name like '%大% 'or name like '%的% 'or name like '%程% 'or name like '%序% 'or name like '%大% 'or name like '%本% ' or name like '%营% ' SQL codeCREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ') RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000)) AS BEGIN DECLARE @idx smallint, @value varchar(8000), @bcontinue bit, @iStrike smallint, @iDelimlength tinyint IF @sDelim = 'Space' BEGIN SET @sDelim = ' ' END SET @idx = 0 SET @sText = LTrim(RTrim(@sText)) SET @iDelimlength = DATALENGTH(@sDelim) SET @bcontinue = 1 IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty')) BEGIN WHILE @bcontinue = 1 BEGIN --If you can find the delimiter in the text, retrieve the first element and --insert it with its index into the return table. IF CHARINDEX(@sDelim, @sText)>0 BEGIN SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1) BEGIN INSERT @retArray (idx, value) VALUES (@idx, @value) END --Trim the element and its delimiter from the front of the string. --Increment the index and loop. SET @iStrike = DATALENGTH(@value) + @iDelimlength SET @idx = @idx + 1 SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike)) END ELSE BEGIN --If you can抰 find the delimiter in the text, @sText is the last value in --@retArray. SET @value = @sText BEGIN INSERT @retArray (idx, value) VALUES (@idx, @value) END --Exit the WHILE loop. SET @bcontinue = 0 END END END ELSE BEGIN WHILE @bcontinue=1 BEGIN --If the delimiter is an empty string, check for remaining text --instead of a delimiter. Insert the first character into the --retArray table. Trim the character from the front of the string. --Increment the index and loop. IF DATALENGTH(@sText)>1 BEGIN SET @value = SUBSTRING(@sText,1,1) BEGIN INSERT @retArray (idx, value) VALUES (@idx, @value) END SET @idx = @idx+1 SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1) END ELSE BEGIN --One character remains. --Insert the character, and exit the WHILE loop. INSERT @retArray (idx, value) VALUES (@idx, @sText) SET @bcontinue = 0 END END END RETURN END 下面是调用示例: declare @no char(100) set @no='abc,cdesefd,efddc,mgns,aa' select * from fn_Split(@no,',') 智能分割字符函数