/************************************************************< xmlnamespace prefix ="o" ns ="urn:schemas-microsoft-com:office:office" />
* SQL象数组一样处理字符串、分割字符串
* Time: 2012-1-11 13:41:55
************************************************************/
--***********************************************
--Author:Quber
--Create Date:2012年月日:37:10
--Desc:获取分隔串元素的个数，如："1,2,3"的结果:3
--select dbo.Get_StrArrayLength('1,2,3',',')的结果：--调用示例
--***********************************************
CREATE FUNCTION [dbo].[Get_StrArrayLength]--此函数为[标量值函数]
(
@str NVARCHAR(4000), --要分割的字符串
@split NVARCHAR(1000) --分隔符号
)
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
SELECT [dbo].[Get_StrArrayLength]('1,2,3,4,61,2,3,4,61,2,3,4,61,2,3,4,61,2,3,4,61,2,3,4,61,2,3,4,61,2,3,4,61,2,3,4,6', ',');
--***********************************************
--Author:Quber
--Create Date:2012年月日:45:55
--Desc:将字符串按照指定的分割符号分离，再取出分离后指定的子字符串
--select dbo.Get_StrArrayStrOfIndex('1,112,3',',',2)的结果:112--调用示例
--***********************************************
CREATE FUNCTION [dbo].[Get_StrArrayStrOfIndex]
(
@str NVARCHAR(4000), --要分割的字符串
@split NVARCHAR(1000), --分隔符号
@index INT --取第几个元素
)
RETURNS VARCHAR(1024)
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
SELECT [dbo].[Get_StrArrayStrOfIndex]('12,asad,43,sd324',',','10');
