T_SQL 中的split函数

create function f_split( @SourceSql varchar ( 8000 ), @StrSeprate varchar ( 10 ))
returns @temp table (a varchar ( 100 ))
-- 实现split功能的函数
-- date:2003-10-14
as
begin
declare @i int
set @SourceSql = rtrim ( ltrim ( @SourceSql )) -- 去掉字符中的空格
set @i = charindex ( @StrSeprate , @SourceSql ) -- 找分割符在字符中的位置
while @i >= 1
begin
insert @temp values ( left ( @SourceSql , @i - 1 ))
set @SourceSql = substring ( @SourceSql , @i + 1 , len ( @SourceSql ) - @i )
set @i = charindex ( @StrSeprate , @SourceSql )
end
if @SourceSql <> ''
insert @temp values ( @SourceSql )
return
end

返回的是一个table，所以执行要用如下格式：select * from dbo.f_split('ABC:BC:C:D:E',':')

－－－－－－－－－－－－－－－－－－－－－－－－－－

SQL实现split函数，自定义分割字符，自定义取出第几个分割字符前的字符串

自定义取出第几个分割字符前的字符串，默认位置（0）
格式：dbo.split(字段名,'分隔字符',取出的第几个字符串)
如果没有分隔的字符，则返回整个字符串。
如果取出的位置字符串的位置超出Index则返回空。

CREATE FUNCTION [ dbo ] . [ split ]
( @str nvarchar ( 4000 ), @code varchar ( 10 ), @no int )
RETURNS varchar ( 200 )
AS
BEGIN

declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen = len ( @code )
set @count = 0
set @indexb = 1


if @no = 0
if charindex ( @code , @str , @indexb ) <> 0
return left ( @str , charindex ( @code , @str , @indexb ) - 1 )
else
return @str

while charindex ( @code , @str , @indexb ) <> 0
begin
set @count = @count + 1
if @count = @no
break
set @indexb = @intLen + charindex ( @code , @str , @indexb )
end


if @count = @no
begin

set @indexe = @intLen + charindex ( @code , @str , @indexb )
if charindex ( @code , @str , @indexe ) <> 0
return substring ( @str , charindex ( @code , @str , @indexb ) + len ( @code ), charindex ( @code , @str , @indexe ) - charindex ( @code , @str , @indexb ) - len ( @code ))
else
return right ( @str , len ( @str ) - charindex ( @code , @str , @indexb ) - len ( @code ) + 1 )

end

return ''

END

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

CREATE Function f_trimstr( @str varchar ( 100 ))

returns varchar ( 100 )

-- 功能：去掉字符串中的所有空格

AS

begin

declare @i int
declare @s1 varchar ( 50 )
declare @result varchar ( 100 )
declare @len int

select @result = ''
select @str = ltrim ( rtrim ( @str ))
select @len = len ( @str )
select @i = 1

while @i <= @len
begin
select @s1 = substring ( @str , @i , 1 )
if ( @s1 <> '' )
begin
select @result = @result + @s1
end
select @i = @i + 1
end

return @result

end

===================================================================================

有分割字符串就有组合字符串,在不使用游标的情况下将表中的一个字段的值组合成一个字符串并以分割符割开

Declare@ttvarchar( 200 )

set @tt = N ''

select@tt = @tt + name + ' , ' fromtable where .............

set @tt = substring(@tt, 0 ,len(@tt))

select@tt

CREATE FUNCTION [ dbo ] . [ split ]
( @str nvarchar ( 4000 ), @code varchar ( 10 ), @no int )
RETURNS varchar ( 200 )
AS
BEGIN

declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen = len ( @code )
set @count = 0
set @indexb = 1


if @no = 0
if charindex ( @code , @str , @indexb ) <> 0
return left ( @str , charindex ( @code , @str , @indexb ) - 1 )
else
return @str

while charindex ( @code , @str , @indexb ) <> 0
begin
set @count = @count + 1
if @count = @no
break
set @indexb = @intLen + charindex ( @code , @str , @indexb )
end


if @count = @no
begin

set @indexe = @intLen + charindex ( @code , @str , @indexb )
if charindex ( @code , @str , @indexe ) <> 0
return substring ( @str , charindex ( @code , @str , @indexb ) + len ( @code ), charindex ( @code , @str , @indexe ) - charindex ( @code , @str , @indexb ) - len ( @code ))
else
return right ( @str , len ( @str ) - charindex ( @code , @str , @indexb ) - len ( @code ) + 1 )

end

return ''

END