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用这个函数,随便你取那个
declare @str varchar(100),@find varchar(100),@firstPosition int,@secondPosition int set @str = '100-825123123123-4565-7878' set @find = '-' set @firstPosition = charindex(@find,@str)+1 set @secondPosition = charindex(@find,@str,@firstPosition) select substring(@str,@firstPosition,@secondPosition-@firstPosition)
declare @number varchar(50) declare @number2 varchar(50) declare @lenth int set @number='12156765-15545-54515' set @lenth=charindex('-',@number)-1 set @number2=substring(@number,@lenth+2,len(@number)) select substring(@number2,1,(charindex('-',@number2)-1))
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用这个函数,随便你取那个
set @str = '100-825123123123-4565-7878'
set @find = '-'
set @firstPosition = charindex(@find,@str)+1
set @secondPosition = charindex(@find,@str,@firstPosition)
select substring(@str,@firstPosition,@secondPosition-@firstPosition)
declare @number2 varchar(50)
declare @lenth int
set @number='12156765-15545-54515'
set @lenth=charindex('-',@number)-1
set @number2=substring(@number,@lenth+2,len(@number))
select substring(@number2,1,(charindex('-',@number2)-1))