select left(字段,charindex('-',字段)-1) ,right(字段,charindex('-',reverse(字段))-1) from T
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) /*--实现split功能 的函数 --date :2005-4-20 --Author :Domino */ 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 goselect * from dbo.f_split('山东:济南:山东:济南',':')select top 1 * from f_split('山东:济南',':') go drop function [dbo].[f_split] go
declare @str varchar(50)
set @str='100元-987元'select substring(@str,0,charindex('-',@str,0))select substring(@str,charindex('-',@str,0)+1,len(@str)-charindex('-',@str,0)+1)
------------------
17 19
,right(字段,charindex('-',reverse(字段))-1)
from T
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
/*--实现split功能 的函数
--date :2005-4-20
--Author :Domino
*/
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
goselect * from dbo.f_split('山东:济南:山东:济南',':')select top 1 * from f_split('山东:济南',':')
go
drop function [dbo].[f_split]
go
declare @s varchar(8000)
declare @s1 varchar(8000)
declare @s2 varchar(8000)
declare @n int
set @s = '100元-987元'
set @n = charindex( '-' , @s , 1)
select @s1 = left(@s,@n - 1)
select @s = stuff(@s , 1, @n , '')
select @s2 = left(@s , len(rtrim(@s)))
--或者
select @s1 = substring(@s , 1, @n - 1)
select @s2 = substring(@s , @n+1 , len(rtrim(@s)) - @n)