alter procedure prc_daihaoming
as
declare @int_return int
declare @start int
declare @str varchar(20)
declare @location int
declare @strs varchar(20)
select @str='2008-06-14'
select @start=1
select @strs=''
select @location=charindex('-',@str,@start)
while(@location<>0)
begin
select @[email protected]+substring(@str,@start,@location-1)
select @[email protected]+1
select @location=charindex('-',@str,@start)
end
print @strs
Exec prc_daihaoming
输出是200806-14 少循环1次 可是SQL里面没有do while 要怎么解决 (必须charindex和substring一起用)
用substring代替split我是会的就是和charindex配合使用没成功
------解决方案--------------------
把2008-06-14 变成20080614
replace('2008-06-14','-','')
or
convert(varchar(8),'2008-06-14',112)
------解决方案--------------------
- SQL code
/* 分解公式 将公式按运算分解到表中*/declare @str varchar(100)declare @i int,@str1 varchar(10),@str2 varchar(10)set @str='3+7+12-13+8-9*123' --要分解的字符串declare @tb table(num varchar(20),operation varchar(2)) --定义保存结果的表--公式分拆set @i=patindex('%[^0-9]%',@str)while @i>0begin select @str1=left(@str,@i-1) ,@str=substring(@str,@i,8000) ,@i=patindex('%[0-9]%',@str) ,@str2=left(@str,@i-1) ,@str=substring(@str,@i,8000) ,@i=patindex('%[^0-9]%',@str) insert into @tb values(@str1,@str2)endif @str<>'' insert into @tb values(@str,'')--显示分拆结果select * from @tb
------解决方案--------------------
- SQL code
replace('2008-06-14','-','')orconvert(varchar(8),'2008-06-14',112)
------解决方案--------------------
- SQL code
select datename(yy,getdate())+datename(mm,getdate())+datename(dd,getdate())或select replace(convert(varchar(10),getdate(),120),'-','')
------解决方案--------------------