1 ALTER FUNCTION [dbo].[Get_StrArrayStrOfIndex] 2 ( 3 @str VARCHAR(1024) , 4 @split VARCHAR(10) , 5 @index INT 6 )--按指定符号分割字符串,返回分割后指定索引的第几个元素 7 RETURNS VARCHAR(1024) 8 AS 9 BEGIN 10 DECLARE @location INT 11 DECLARE @start INT 12 DECLARE @next INT 13 DECLARE @seed INT 14 15 SET @str = LTRIM(RTRIM(@str)) 16 SET @start = 1 17 SET @next = 1 18 SET @seed = LEN(@split) 19 SET @location = CHARINDEX(@split, @str) 20 21 WHILE @location <> 0 22 AND @index > @next 23 BEGIN 24 SET @start = @location + @seed 25 SET @location = CHARINDEX(@split, @str, @start) 26 SET @next = @next + 1 27 END 28 29 IF @location = 0 30 SELECT @location = LEN(@str) + 1 31 --这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。 32 RETURN SUBSTRING(@str,@start,@location-@start) 33 END