34,590
社区成员
发帖
与我相关
我的任务
分享
select spmc, case when CHARINDEX('*',spmc)>0 then SUBSTRING(spmc,1,CHARINDEX('*',spmc)-1)else spmc end spm1,
case when CHARINDEX('*',spmc)>0 then SUBSTRING(spmc,CHARINDEX('*',spmc)+1,LEN(spmc)-CHARINDEX('*',spmc))else spmc end spm2
from a
select spmc,substring(spmc,1,3) spmc1,substring(spmc,5,8) spmc2 from a
或者你写个函数,到时候调用吧
create function split
(
@str nvarchar(200),
@i int
)
returns nvarchar(200)
as
begin
declare @len int
declare @substr nvarchar(200)
declare @wz int
if CHARINDEX('*',@str)>0
begin
set @len= LEN(@str)
set @wz=CHARINDEX('*',@str,1)
if @i>0
set @substr=SUBSTRING(@str,1,@wz-1)
else
set @substr=SUBSTRING(@str,@wz+1,@len-@wz)
end
else
set @substr=@str
return @substr
end
select tname,left(tname,CHARINDEX('*',tname)-1),RIGHT(tname,LEN(tname)-CHARINDEX('*',tname))
from test1
select spmc,substring(spmc1,1,3),substring(spmc2,5,8) from a
with table1 as
(
select '170*1.63' col1
)
select col1
, SUBSTRING(col1, 1, patindex('%*%', col1)-1)
, SUBSTRING(col1, patindex('%*%', col1)+1, 100)
from table1