第一步,先把答案分开:create table #t(
answer varchar(300)
)declare @answer varchar(3000)
set @answer='3A,4B,45C,103D,6你好,34我爱你,15-103,67-1963'declare @sql varchar(7000)
set @sql='insert #t select '''+ replace(@answer,',',''' union all select ''')+''''exec (@sql)
select * from #t结果:
answer
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3A
4B
45C
103D
6你好
34我爱你
15-103
67-1963
answer varchar(300)
)declare @answer varchar(3000)
set @answer='3A,4B,45C,103D,6你好,34我爱你,15-103,67-1963'declare @sql varchar(7000)
set @sql='insert #t select '''+ replace(@answer,',',''' union all select ''')+''''exec (@sql)
select * from #t结果:
answer
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3A
4B
45C
103D
6你好
34我爱你
15-103
67-1963
我要把这些数据里面左边的数字提取出来,比如,3,4,45,103,6,34,15,67这些,而把后面的A,B,C,D,你好,我爱你,-103,-1963去掉
create function GetNum(@str varchar(3000))
returns int
as
begin
declare @r int
declare @tr varchar(300)
declare @ts varchar(300)
set @ts=@str
set @tr=''while (left(@ts,1) in ('0','1','2','3','4','5','6','7','8','9'))
begin
set @tr=@tr+left(@ts,1)
set @ts=right(@ts,len(@ts)-1)
end
set @r=cast(@tr as int)
return @r
end
gocreate function GetAns(@str varchar(3000))
returns varchar(300)
as
begin
declare @r varchar(300)
declare @tr varchar(300)
declare @ts varchar(300)
set @ts=@str
set @tr=''while (left(@ts,1) in ('0','1','2','3','4','5','6','7','8','9'))
begin
set @tr=@tr+left(@ts,1)
set @ts=right(@ts,len(@ts)-1)
endif (left(@ts,1)='-')
set @r=right(@ts,len(@ts)-1)
else
set @r=@ts
return @r
end
go
--查询select dbo.GetNum(answer) as 题号,dbo.GetAns(answer) as 答案 from #t
如果“answer里面已经你说的这些数据了”,不必操作第一步