sql获取数组长度

需求：获取字符串数组1,2,3,4的长度，当然也可以是其他分隔符1|2|3等

方法：通过自定义函数来实现

/*
       获取字符串数组长度
*/
if exists (select 1 from sysobjects where id = object_id('Get_StrArrayLength' ))
   drop Function Get_StrArrayLength
go
create function Get_StrArrayLength
(
  @str varchar(5000 ),  -- 要分割的字符串
  @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

用法：

PRINT dbo.Get_StrArrayLength('1,2,3',',')