/*
一、按指定符号分割字符串,返回分割后的元素个数,方法很简单,就是看字符串中存在多少个分隔符号,然后再加一,就是要求的结果。
--Alter function Get_StrArrayLength
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
(
@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
调用示例:select dbo.Get_StrArrayLength('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,
36,37,38,39,40,41,42,43,44,45,46,47,48',',')
返回值:48
二、按指定符号分割字符串,返回分割后指定索引的第几个元素,象数组一样方便
--alter function Get_StrArrayStrOfIndex
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
-- 这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring ( @str , @start , @location - @start )
end
(
@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
-- 这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring ( @str , @start , @location - @start )
end
调用示例:select dbo.Get_StrArrayStrOfIndex('8,9,4',',',2)
返回值:9
三、结合上边两个函数,象数组一样遍历字符串中的元素
declare
@str
varchar
(
5000
)
set @str = ' 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48 '
print dbo.Get_StrArrayLength( @str , ' , ' )
declare @next int
set @next = 1
while @next <= dbo.Get_StrArrayLength( @str , ' , ' )
begin
print dbo.Get_StrArrayStrOfIndex( @str , ' , ' , @next ) -- --输出数组中的值
set @next = @next + 1
end
set @str = ' 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48 '
print dbo.Get_StrArrayLength( @str , ' , ' )
declare @next int
set @next = 1
while @next <= dbo.Get_StrArrayLength( @str , ' , ' )
begin
print dbo.Get_StrArrayStrOfIndex( @str , ' , ' , @next ) -- --输出数组中的值
set @next = @next + 1
end
调用结果:
1
2
3
4
5
四、自己加一个函数,检查一个元素是否在数组中
Alter
function
CheckStrInArr(
@s
as
varchar
(
50
),
@sArr
as
varchar
(
5000
))
returns
int
as
begin
declare @str varchar ( 5000 )
set @str = @sArr
declare @next int
declare @ret int
set @ret = 0
set @next = 1
while @next <= dbo.Get_StrArrayLength( @str , ' , ' )
begin
if dbo.Get_StrArrayStrOfIndex( @str , ' , ' , @next ) = @s
begin
set @ret = 1 ;
end
set @next = @next + 1
end
return @ret
end
as
begin
declare @str varchar ( 5000 )
set @str = @sArr
declare @next int
declare @ret int
set @ret = 0
set @next = 1
while @next <= dbo.Get_StrArrayLength( @str , ' , ' )
begin
if dbo.Get_StrArrayStrOfIndex( @str , ' , ' , @next ) = @s
begin
set @ret = 1 ;
end
set @next = @next + 1
end
return @ret
end