字符串处理？

[xyjxzbj]

需求：
1.数据库中表ImageData:
Imageno | keywords　　　　　　 | KeywordsinCHN　　
IS150-185 2,2 people,2persons,accomplice,admiration,admire,
IS150-186 ages,amused,at camera,attentive,black,
IS150-187 two persons,white,worker,workers,workmate,workmates,
…… ……

2.数据库中表Translate
keyword | 中文 | 複數 | 過去式
ages XXX XXX XXX
two persons 两人 XXX XXX
white 白色 XXX XXX
…… …… …… ……

表ImageData的字段keywords下是英文关键字，现在要到表Translate中找到对应的中文，再填入到ImageData的字段KeywordsinCHN中，举例：
表ImageData中Imageno为IS150-187的记录，其keywords为two persons,white,worker,workers,workmate,workmates,那么到表Translate中依次寻找对应的记录；如two persons,就在Translate中找複數、過去式、keyword这三个字段中任一个为two persons的记录；将这条记录的中文插入表ImageData的KeywordsinCHN字段中并加上一个","，最后表ImageData中Imageno为IS150-187的记录字段KeywordsinCHN下可能是“两人,白色,”；如果在Translate中找不到对应的，如worker和workers没有找到对应，则将这俩个词插入到一个零时表存起来：
keword

worker
workers

……

[xyjxzbj]

我看看

[xyjxzbj]

CREATE PROCEDURE xyDataManage
AS
/***************** 真正的处理命令开始 *******************************/
-- 字符串分割函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
-- 导入excel数据
select * into #xy20060607 from openrowset('Microsoft.Jet.OleDB.4.0',
'Excel 8.0;HDR=Yes;Database=F:\datamanage\upexcel\Imagesource2.xls',ImageData$)
-- 删除表ImageData中已有的
delete from #xy20060607
where Imageno in (select Imageno from ImageData)
-- 建立零时表 以便储存找不到中文的KeyWord
create table #xy200606071(KeyWord varchar(100))
-- 定义游标MyTestCursor：
declare MyExcelCursor cursor
for select Imageno,keywords,KeywordsinCHN from #xy20060607
/*从表中选取三个字段*/
-- 打开游标MyTestCursor：
open MyExcelCursor
declare @Imageno nvarchar(255)
declare @keywords nvarchar(4000)
--fetch取出游标所指的记录，并将记录结果存入到变量中:
fetch from MyExcelCursor into @Imageno,@keywords
/***************** begin of loop *******************************/
while @@FETCH_STATUS = 0
Begin
/***************** 开始处理@keywords *******************************/
declare MyKeywordsCursor cursor
for SELECT col FROM f_splitSTR(@keywords,',')
open MyKeywordsCursor
declare @keyword nvarchar(100)
declare @chinese nvarchar(100)
fetch from MyKeywordsCursor into @keyword
while @@FETCH_STATUS = 0
Begin
/***************** 到翻译表去找对应的中文 开始 *******************************/
--到表按 plurality、past、keyword的顺序寻找对应的“中文”
SET @chinese = ''
SELECT @chinese=chinese FROM Translate where keyword = @keyword
SELECT @chinese=chinese FROM Translate where past = @keyword
SELECT @chinese=chinese FROM Translate where plurality = @keyword
IF （@chinese = ''）
-- 如果找不到，将这个 keyword 插入到零时表
insert into #xy200606071 select @keyword
else
-- 如果找到了，将这个 中文插入到表零时表#xy20060607
update ImageData set KeywordsinCHN=@chinese where Imageno = （@Imageno+','）

/***************** 到翻译表去找对应的中文 结束 *******************************/
fetch next from MyKeywordsCursor into @keyword
End
close MyKeywordsCursor
deallocate MyKeywordsCursor
/***************** 结束处理@keywords *******************************/
fetch next from MyTestCursor into @Imageno,@keywords
End
--关 闭游标，释放游标：
close MyExcelCursor
deallocate MyExcelCursor
/***************** end of loop *******************************/
--导出Excel数据
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=F:\datamanage\downexcel\trans.xls',Trans$)select * from #xy200606071
drop table #xy200606071
drop table #xy20060607
drop FUNCTION f_splitSTR
/***************** 处理结束 *******************************/
return 'ok'
GO

[xyjxzbj]

请帮我看看这里有哪些错误？

[撸大湿]

CREATE PROCEDURE xyDataManage
AS
/***************** 真正的处理命令开始 *******************************/
-- 字符串分割函数
CREATE FUNCTION f_splitSTR







.........
有这么申明的吗

[撸大湿]

不知道楼主的这段代码能不能跑起来

效率很低

--CREATE PROCEDURE xyDataManage
--AS
/***************** 真正的处理命令开始 *******************************/
-- 字符串分割函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
-- 导入excel数据
select * into #xy20060607 from openrowset('Microsoft.Jet.OleDB.4.0',
'Excel 8.0;HDR=Yes;Database=F:\datamanage\upexcel\Imagesource2.xls',ImageData$)
-- 删除表ImageData中已有的
delete from #xy20060607
where Imageno in (select Imageno from ImageData)
-- 建立零时表 以便储存找不到中文的KeyWord
create table #xy200606071(KeyWord varchar(100))
-- 定义游标MyTestCursor：
declare MyExcelCursor cursor
for select Imageno,keywords,KeywordsinCHN from #xy20060607
/*从表中选取三个字段*/
-- 打开游标MyTestCursor：
open MyExcelCursor
declare @Imageno nvarchar(255)
declare @keywords nvarchar(4000)
--fetch取出游标所指的记录，并将记录结果存入到变量中:
fetch from MyExcelCursor into @Imageno,@keywords
/***************** begin of loop *******************************/
while @@FETCH_STATUS = 0
Begin
/***************** 开始处理@keywords *******************************/
declare MyKeywordsCursor cursor
for SELECT col FROM f_splitSTR(@keywords,',')
open MyKeywordsCursor
declare @keyword nvarchar(100)
declare @chinese nvarchar(100)
fetch from MyKeywordsCursor into @keyword
while @@FETCH_STATUS = 0
Begin
/***************** 到翻译表去找对应的中文 开始 *******************************/
--到表按 plurality、past、keyword的顺序寻找对应的“中文”
SET @chinese = ''
SELECT @chinese=chinese FROM Translate where keyword = @keyword
SELECT @chinese=chinese FROM Translate where past = @keyword
SELECT @chinese=chinese FROM Translate where plurality = @keyword
IF (@chinese = '')
-- 如果找不到，将这个 keyword 插入到零时表
insert into #xy200606071 select @keyword
else
-- 如果找到了，将这个 中文插入到表零时表#xy20060607
update ImageData set KeywordsinCHN=@chinese where Imageno = (@Imageno+',')

/***************** 到翻译表去找对应的中文 结束 *******************************/
fetch next from MyKeywordsCursor into @keyword
End
close MyKeywordsCursor
deallocate MyKeywordsCursor
/***************** 结束处理@keywords *******************************/
fetch next from MyTestCursor into @Imageno,@keywords
End
--关 闭游标，释放游标：
close MyExcelCursor
deallocate MyExcelCursor
/***************** end of loop *******************************/
--导出Excel数据
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=F:\datamanage\downexcel\trans.xls',Trans$)select * from #xy200606071
drop table #xy200606071
drop table #xy20060607
drop FUNCTION f_splitSTR
/***************** 处理结束 *******************************/
return
GO

[xyjxzbj]

用tntzbzc(华裔大魔王—抗日英雄—抗日要从娃娃抓起）的改了下，现在有个问题是：excel里我只有六条记录，但导入到sqlserver中多了很多所有字段为null的记录，这个是为甚么？

if exists(select * from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#xy20060607'))
drop table #xy20060607
if exists(select * from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#xy200606071'))
drop table #xy200606071
-- 建立数据表
create table #xy20060607(Imageno varchar(100),keywords varchar(7000),KeywordsinCHN varchar(7000))
-- 导入excel数据
insert into #xy20060607(Imageno,keywords) select Imageno,CAST(keywords AS varchar(7000)) from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=Yes;Database=F:\datamanage\upexcel\Imagesource.xls',ImageData$)
-- 删除表ImageData中已有的
delete from #xy20060607
where Imageno in (select Imageno from ImageData)
create table #xy200606071(KeyWord varchar(100),chinese varchar(100),plurality varchar(100),past varchar(100)) ----记录无KEYWORD的字段
declare @N1 int,@INO varchar(100),@KW1 varchar(7000),@KW2 varchar(100),@KW3 varchar(7000),@COUNT INT
select @count=count(1) from #xy20060607
while @count>0
begin
set @KW3=N''
if isnull(@INO,'')=''
select top 1 @INO=Imageno,@KW1=keywords from #xy20060607 order by Imageno
else
select top 1 @INO=Imageno,@KW1=keywords from #xy20060607 where Imageno>@INO order by Imageno
while len(@KW1)-len(replace(@KW1,',',''))>0
begin
select @N1=charindex(',',@KW1)
select @KW2=left(@KW1,@N1-1),@KW1=substring(@KW1,@N1+1,LEN(@KW1)-@N1)
select @KW3=ISNULL(@KW3+[chinese]+',','') from Translate where keyword=@KW2 or [plurality]=@KW2 or [past]=@KW2
if @@rowcount=0
insert into #xy200606071 select @KW2
end
update #xy20060607 set KeywordsinCHN=@KW3 where Imageno=@INO
set @count=@count-1
end
--select * from #xy20060607
--select distinct * from #xy200606071
-- 插入到数据表
insert into ImageData(Imageno,keywords,KeywordsinCHN) select Imageno,keywords,KeywordsinCHN from #xy20060607
-- 导出到Excel
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=F:\datamanage\transexcel\trans.xls',Trans$)select distinct * from #xy200606071
drop table #xy200606071,#xy20060607





if exists(select * from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#xy200606072'))
drop table #xy200606072
--翻译完成后，导入到数据库
create table #xy200606072(keyword varchar(100),chinese varchar(100),plurality varchar(100),past varchar(100))
-- 导入excel数据到零时表
insert into #xy200606072 (keyword,chinese,plurality,past) select keyword,chinese,plurality,past from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=Yes;Database=F:\datamanage\upexcel\translate.xls',trans$)
select * from #xy200606072
-- 删除表Translate中已有的数据
delete from #xy200606072
where keyword in (select keyword from Translate)
-- 导入到表Translate
insert into Translate(keyword,chinese,plurality,past) select keyword,chinese,plurality,past from #xy200606072
drop table #xy200606072

select * from Translate
select * from #xy200606072

[xyjxzbj]

知道了，还有一个问题
create table #xy200606071(KeyWord varchar(100),chinese varchar(100),plurality varchar(100),past varchar(100))
insert into #xy200606071 select @KW2,null,null,null
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=F:\datamanage\transexcel\trans.xls',Trans$) select distinct * from #xy200606071 where KeyWord is not null

结果应该是excel里 KeyWord 列下有值，但实际上是chinese列下有值，错了一栏，这是什么原因？

[撸大湿]

很奇怪的问题，无法解释

换成这样试试

insert into #xy200606071(KeyWord) select @KW2
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=F:\datamanage\transexcel\trans.xls',Trans$) select distinct * from #xy200606071 where KeyWord is not null

[昵称被占用了]

update ImageData
set KeywordsinCHN　=keywords

while exists (
select 1 from ImageData a,(
select keyword,中文
from Translate
union
select 複數,中文
from Translate
union
select 過去式,中文
from Translate
) as t
where ','+a.KeywordsinCHN+',' like '%,'+t.keyword+',%'
)

update a
set KeywordsinCHN=substring(replace(','+a.KeywordsinCHN+',',','+t.keyword+',',','+t.中文+','),2,len(replace(','+a.KeywordsinCHN+',',','+t.keyword+',',','+t.中文+','))-2)
from ImageData a,(
select keyword,中文
from Translate
union
select 複數,中文
from Translate
union
select 過去式,中文
from Translate
) as t
where ','+a.KeywordsinCHN+',' like '%,'+t.keyword+',%'

[昵称被占用了]

很不好意思，效率很成问题

[撸大湿]

问题是
如worker和workers没有找到对应，则将这俩个词插入到一个零时表存起来：
keword

worker
workers


这个有点麻烦

[xyjxzbj]

这个要处理的一般是几万条记录，效率得考虑。另外谢谢你得代码，因为我很多地方不太懂，能加些注释就好了，谢谢！

[xyjxzbj]

按我的理解：
循环表ImageData,读取每一条记录（游标？）开始
在记录里处理keywords这个字段值，分割这个字符串
循环这个字符串集合（数组）
到表Translate里查找对应
如果找到，插入到表ImageData
如果找不到，插入到零时表
字符串集合循环下一条
字符串集合循环结束
循环表ImageData下一条
循环表ImageData结束

[xyjxzbj]

顶下

[撸大湿]

--1.数据库中表ImageData:
create table ImageData (Imageno varchar(100),keywords varchar(100),KeywordsinCHN varchar(8000))
insert into ImageData select 'IS150-185','2,2 people,2persons,accomplice,admiration,admire,bb,',''
insert into ImageData select 'IS150-186','ages,amused,at camera,attentive,black,XXX,BB,TT,',''
insert into ImageData select 'IS150-187','two persons,white,worker,workers,workmate,workmates,',''
--…… ……

--2.数据库中表Translate
create table Translate(keyword varchar(100),中文 varchar(100),複數 varchar(100),過去式 varchar(100))
insert into Translate select 'ages','年龄','tt','SS'
insert into Translate select 'two persons','两人','aa','ABC'
insert into Translate select 'white','白色','bb','CC'

create table #临时表(KeyWords varchar(1000)) ----记录无KEYWORD的字段

declare @N1 int,@INO varchar(100),@KW1 varchar(100),@KW2 varchar(100),@KW3 varchar(100),@COUNT INT
select @count=count(1) from ImageData
while @count>0
begin
set @KW3=N''
if isnull(@INO,'')=''
select top 1 @INO=Imageno,@KW1=keywords from ImageData order by Imageno
else
select top 1 @INO=Imageno,@KW1=keywords from ImageData where Imageno>@INO order by Imageno
while len(@KW1)-len(replace(@KW1,',',''))>0
begin
select @N1=charindex(',',@KW1)
select @KW2=left(@KW1,@N1-1),@KW1=substring(@KW1,@N1+1,LEN(@KW1)-@N1)
select @KW3=ISNULL(@KW3+[中文]+',','') from Translate where keyword=@KW2 or [複數]=@KW2 or [過去式]=@KW2
if @@rowcount=0
insert into #临时表 select @KW2
end
update ImageData set KeywordsinCHN=@KW3 where Imageno=@INO
set @count=@count-1
end
select * from ImageData
select * from #临时表
drop table ImageData,Translate,#临时表