时间:2021-07-01 10:21:17 帮助过:2人阅读
获取逗号分隔的字符串的个数
CREATE function [dbo].[Get_StrArrayLength] ( @str nvarchar(max), --要分割的字符串 @split varchar(10) --分隔符号 ) returns int as begin declare @location int declare @start int declare @length int set @str=ltrim(rtrim(@str)) set @location=charindex(@split,@str) set @length=1 while @location<>0 begin set @start=@location+1 set @location=charindex(@split,@str,@start) set @length=@length+1 end return @length end GO
按照某个符号分割字符串 翻来一张表
CREATE FUNCTION [dbo].[SplitStringToTable] ( @String nvarchar(4000), --格式如:“1,2,3,4,” @SplitChar nvarchar(10) --分割的字符:“,” ) RETURNS @table Table(ID varchar(100)) AS BEGIN DECLARE @Index INT SET @Index = 0 IF @String <> ‘‘ Begin IF RIGHT(@String,1)<> @SplitChar SET @String = @String + @SplitChar IF LEFT(@String,1)= @SplitChar SET @String = STUFF(@String, 1, 1, ‘‘) End WHILE CHARINDEX(@SplitChar,@String,@Index) > 0 BEGIN INSERT INTO @table(ID) VALUES (SUBSTRING(@String, @Index, CHARINDEX(@SplitChar, @String, @Index) - @Index)) SET @index = CHARINDEX(@SplitChar, @String, @Index) + 1 END RETURN END GO
sqlserver几个好用的表值函数和标量函数
标签: