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
--根据分隔符 和位置 ,取得元素
CREATE FUNCTION Get_StrArrayStrOfIndex
( @str varchar(5000) , --要分割的字符串
@split varchar(10) , --分隔符号
@index int --取第几个元素
)
RETURNS varchar(5000)
AS
BEGIN
DECLARE @location INT
DECLARE @start INT
DECLARE @next INT
DECLARE @seed INT
SET @str = LTRIM(RTRIM(@str))
SET @start = 1
SET @next = 1
SET @seed = LEN(@split)
SET @location = CHARINDEX(@split, @str)
WHILE @location <> 0
AND @index > @next
BEGIN
SET @start = @location + @seed
SET @location = CHARINDEX(@split, @str, @start)
SET @next = @next + 1
END
IF @location = 0
SELECT @location = LEN(@str) + 1
RETURN SUBSTRING(@str,@start,@location-@start)
END