如果不是: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--调用: --id1: select distinct dbo.getstrofindex('1-2-4',1) from 你的表--id2: select distinct dbo.getstrofindex('1-2-4',2) from 你的表--id3: select distinct dbo.getstrofindex('1-2-4',3) from 你的表
调用大力的: 1: Select distinct dbo.getstrofindex(id,1) as id1 from myTable where dbo.getstrofindex(id,1) > '' 2: Select distinct dbo.getstrofindex(id,2) as id2 from myTable where dbo.getstrofindex(id,2) > '' 3: Select distinct dbo.getstrofindex(id,3) as id3 from myTable where dbo.getstrofindex(id,3) > '' 4: Select distinct dbo.getstrofindex(id,4) as id4 from myTable where dbo.getstrofindex(id,4) > ''
select distinct dbo.getstrofindex(列名,3) from 你的表
我试了一下,可以了,但是把上面的代码怎么放进程序里呢? 还有,如果创建2遍函数好象就要报错,这个函数是放在哪里的呢?是不是在程序里使用select distinct dbo.getstrofindex(列名,3) from 你的表 这个语句时先要在sql server2000中把这个函数建立好呢?
对你先建立好过程,只要调用就可以了。select distinct dbo.getstrofindex(列名,3) as 列名 from 你的表select distinct dbo.getstrofindex(列名,2) as 列名 from 你的表和调用普通的select语句一样。
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--调用:
--id1:
select distinct dbo.getstrofindex('1-2-4',1) from 你的表--id2:
select distinct dbo.getstrofindex('1-2-4',2) from 你的表--id3:
select distinct dbo.getstrofindex('1-2-4',3) from 你的表
1:
Select distinct dbo.getstrofindex(id,1) as id1 from myTable where dbo.getstrofindex(id,1) > ''
2:
Select distinct dbo.getstrofindex(id,2) as id2 from myTable where dbo.getstrofindex(id,2) > ''
3:
Select distinct dbo.getstrofindex(id,3) as id3 from myTable where dbo.getstrofindex(id,3) > ''
4:
Select distinct dbo.getstrofindex(id,4) as id4 from myTable where dbo.getstrofindex(id,4) > ''
还有,如果创建2遍函数好象就要报错,这个函数是放在哪里的呢?是不是在程序里使用select distinct dbo.getstrofindex(列名,3) from 你的表 这个语句时先要在sql server2000中把这个函数建立好呢?