create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1
select @location = charindex('|||',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex('|||',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1
select @str_return = substring(@str,@start,@location -@start)
if (@index <> @next ) select @str_return = ''
return @str_return
end
go
----调用:
select dbo.getstrofindex('a|||b|||c|||d|||e',3)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1
select @location = charindex('|||',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex('|||',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1
select @str_return = substring(@str,@start,@location -@start)
if (@index <> @next ) select @str_return = ''
return @str_return
end
go
----调用:
select dbo.getstrofindex('a|||b|||c|||d|||e',3)
解决方案 »
- 为什么我的这条修改语句不对呢?
- 存储过程中使用动态sql语句,执行时输入参数提示列名无效
- 求!!!sql 涵数 判断是否为数字
- dbcc shrinkfile 和 dbcc shrinkdatabase 的用法
- 统计每个月的数据,求SQL语句的实现
- sql server dbo角色与权限问题
- 我的sql2000为什么认不到硬盘呢?
- 如何将SELECT COUNT(*)的结果存入一变量?
- 为什么这条语句执行要3分钟?
- 16位转换64位
- 如何不通过‘KILL’来中止正在执行‘select’的进程?
- 请问SQL 2000 SERVER必须要安装在WIN 2000 SERVER上吗?SQL70不是都可以安装在WIN 2000 PRO上吗?
如果用“select dbo.getstrofindex('a|||b|||c|||d|||e',3)”
则返回“||c”
而“select dbo.getstrofindex('a|||b|||c|||d|||e',1)”,没有问题,返回"a"
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1
select @location = charindex('|||',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex('|||',@str,@start)
select @next =@next +1
end
select @location =len(@str)+1 where @location =0
select @str_return = substring(@str,@start+2,@location -@start-2)
if (@index <> @next ) select @str_return = ''
return @str_return
end
go
----调用:
select dbo.getstrofindex('a|||b|||c|||d|||e',5)godrop function getstrofindex