求一SQL语句 现在我有一字符串string=“ABC – DEF – GHI – JKL – MNO – PQR – STU”(中间有空格和-)我现在想通过SQL游标实现以下功能,将字符串插入到table english中(扫描该字符串,遇到空格或“-”即插入到下一行记录),插入后的结果如下:(no 为自动编号,name为扫描到的字符串信息)no name
1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 PQR
7 STU请哪位大哥大姐帮忙一下,谢谢
1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 PQR
7 STU请哪位大哥大姐帮忙一下,谢谢
SET @s='ABC – DEF – GHI – JKL – MNO – PQR – STU';SELECT
LTRIM(RTRIM(B.x.value('.','varchar(20)'))) AS name,
B.x.value('for $i in .
return count($i/../*[$i>>.])+1','int') AS no
FROM(
SELECT
CONVERT(XML,'<v>'+REPLACE(@s,'–','</v><v>')+'</v>') AS s
) AS A
CROSS APPLY A.s.nodes('v') AS B(x)/*
name no
-------------------- -----------
ABC 1
DEF 2
GHI 3
JKL 4
MNO 5
PQR 6
STU 7(7 行受影响)*/
set @str = 'ABC – DEF – GHI – JKL – MNO – PQR – STU'
delcare @a int
set @a = 1
while @a < len(@str)
begin
insert into tb(name) values(substring(@str,@a,3)
set @a=@a+7
end
declare @str varchar(100),@i int,@j int
set @str= 'ABC – DEF – GHI – JKL – MNO – PQR – STU'
set @str=rtrim(ltrim(@str))
set @i=charindex(' – ',@str)
set @j=1
while @i>=1
begin
insert into tb values (left(@str,@i-1),@j)
set @j=@j+1
set @str=substring(@str,@i+3,len(@str)-@i)
select @str
set @i=charindex(' – ',@str)
end
if @str<>''
insert into tb values (@str,@j)
returnselect * from tb
drop table tb
create table tb(name varchar(10),no int)
declare @str varchar(100)
set @str = 'ABC – DEF – GHI – JKL – MNO – PQR – STU'
declare @a int,@i int
select @a = 1,@i=1
while @a < len(@str)
begin
insert into tb(name,no) values(substring(@str,@a,3),@i)
select @a = @a+6,@i=@i+1
end
select * from tb
drop table tbname no
---------- -----------
ABC 1
DEF 2
GHI 3
JKL 4
MNO 5
PQR 6
STU 7(所影响的行数为 7 行)
只有是遇到空格或-的时候,不是固定为三个字符,我自己又根据大家的提示修改了一下
但只有空格或-的时候才可以,如果空格和-都有的情况或者有一个以上空格的情况就不对了,该高手帮忙改进一下写法,非常谢谢create table #tb(name varchar(10),no int)
declare @str varchar(100)
set @str = 'ABC DEFGH IJKLMNO PQ ASGSD SDDD'
declare @a int,@i int,@len int,@count int
select @a = 1, @i = 0, @count = 1
select @len=len(@str)
while @i<@len
begin
while (select substring(@str,@a,1))<>' '
begin
select @a=@a+1,@i=@i+1
end
if (select charindex(' ',@str))<>0
begin
insert into #tb(name,no) values(substring(@str,1,@i),@count)
end
else
begin
insert into #tb(no,name) values(@count,substring(@str,1,@i))
break
end
select @str=right(@str,@len-@a)
select @len=len(@str)
select @a=1, @i=0, @count = @count + 1
end
select * from #tb
drop table #tb
上面那串应该得到的结果是不是下面那种情况?
ABC 1
DEFGH 2
IJKLMNO 3
PQ 4
ASGSD 5
SDDD 6
我要得到的结果是no name
1 ABC
2 DEFGH
3 IJKLMNO
4 PQ
5 AS
6 GSD
7 SDDD