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
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@intLen
int
declare
@count
int
declare
@indexb
int
declare
@indexe
int
set
@intLen
=
len
(
@code
)
set
@count
=
0
set
@indexb
=
1
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@no
=
0
if
charindex
(
@code
,
@str
,
@indexb
)
<>
0
return
left
(
@str
,
charindex
(
@code
,
@str
,
@indexb
)
-
1
)
else
return
@str
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
while
charindex
(
@code
,
@str
,
@indexb
)
<>
0
begin
set
@count
=
@count
+
1
if
@count
=
@no
break
set
@indexb
=
@intLen
+
charindex
(
@code
,
@str
,
@indexb
)
end
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@count
=
@no
begin
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
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
)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
return
''
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
END
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
---------------------------------
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
CREATE
FUNCTION
[
dbo
]
.
[
split
]
(
@str
nvarchar
(
4000
),
@code
varchar
(
10
),
@no
int
)
RETURNS
varchar
(
200
)
AS
BEGIN
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@intLen
int
declare
@count
int
declare
@indexb
int
declare
@indexe
int
set
@intLen
=
len
(
@code
)
set
@count
=
0
set
@indexb
=
1
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@no
=
0
if
charindex
(
@code
,
@str
,
@indexb
)
<>
0
return
left
(
@str
,
charindex
(
@code
,
@str
,
@indexb
)
-
1
)
else
return
@str
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
while
charindex
(
@code
,
@str
,
@indexb
)
<>
0
begin
set
@count
=
@count
+
1
if
@count
=
@no
break
set
@indexb
=
@intLen
+
charindex
(
@code
,
@str
,
@indexb
)
end
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@count
=
@no
begin
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
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
)
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
return
''
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
END
![](https://hanguoji.cnblogs.com/Images/OutliningIndicators/None.gif)
转:http://apps.hi.baidu.com/share/detail/4523777