参考:字符串的替换http://topic.csdn.net/u/20091207/14/170a32c6-b625-4634-b3d1-000bd44575ee.html?62065有这样的数据表table1, Col1 Col2 IE Export and Import IE Exp&Imp CO CORPORATION CO COR CO Company HK HONGKONG 现在想写一个sql函数, 把输入的字符串根据table1中的内容进行转换. 例如 China Export and Import company, 输出的结果应该是 china IE CO 我想应该是对table1进行循环, 如果有col2的字符串在输入的字符串出现, 就用col1来代替. 但就是不知道怎样用sql来实现 --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([Col1] varchar(20),[Col2] varchar(20)) insert [TB] select 'IE','Export and Import' union all select 'IE','Exp&Imp' union all select 'CO','CORPORATION' union all select 'CO','COR' union all select 'CO','Company' union all select 'HK','HONGKONG'select * from [TB]declare @s varchar(200) set @s='China Export and Import company' select @s=replace(@s,col2,col1) from TB select @s/*
---------------------- China IE CO(所影响的行数为 1 行)*/ drop table TBcreate table tb(Col1 varchar(10), Col2 varchar(50)) insert into tb values('IE' , 'Export and Import') insert into tb values('IE' , 'Exp&Imp') insert into tb values('CO' , 'CORPORATION') insert into tb values('CO' , 'COR') insert into tb values('CO' , 'Company') insert into tb values('HK' , 'HONGKONG') godeclare @Col1 varchar(10),@Col2 varchar(50); declare @col3 varchar(50) set @col3 = 'China Export and Import company' declare cur cursor fast_forward for select col1,col2 from tb; open cur; fetch next from cur into @Col1,@Col2; while @@fetch_status=0 begin if charindex(@Col2 , @col3) > 0 set @col3 = stuff(@col3 , charindex(@Col2 , @col3) , len(@Col2) , @Col1 ) fetch next from cur into @Col1,@Col2; end print @col3close cur;deallocate cur;drop table tb /* China IE CO */
IE Exp&Imp
CO CORPORATION
CO COR
CO Company
HK HONGKONG 现在想写一个sql函数, 把输入的字符串根据table1中的内容进行转换. 例如 China Export and Import company, 输出的结果应该是 china IE CO 我想应该是对table1进行循环, 如果有col2的字符串在输入的字符串出现, 就用col1来代替. 但就是不知道怎样用sql来实现
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Col1] varchar(20),[Col2] varchar(20))
insert [TB]
select 'IE','Export and Import' union all
select 'IE','Exp&Imp' union all
select 'CO','CORPORATION' union all
select 'CO','COR' union all
select 'CO','Company' union all
select 'HK','HONGKONG'select * from [TB]declare @s varchar(200)
set @s='China Export and Import company'
select @s=replace(@s,col2,col1) from TB
select @s/*
----------------------
China IE CO(所影响的行数为 1 行)*/
drop table TBcreate table tb(Col1 varchar(10), Col2 varchar(50))
insert into tb values('IE' , 'Export and Import')
insert into tb values('IE' , 'Exp&Imp')
insert into tb values('CO' , 'CORPORATION')
insert into tb values('CO' , 'COR')
insert into tb values('CO' , 'Company')
insert into tb values('HK' , 'HONGKONG')
godeclare @Col1 varchar(10),@Col2 varchar(50);
declare @col3 varchar(50)
set @col3 = 'China Export and Import company'
declare cur cursor fast_forward for
select col1,col2 from tb;
open cur;
fetch next from cur into @Col1,@Col2;
while @@fetch_status=0
begin
if charindex(@Col2 , @col3) > 0
set @col3 = stuff(@col3 , charindex(@Col2 , @col3) , len(@Col2) , @Col1 )
fetch next from cur into @Col1,@Col2;
end
print @col3close cur;deallocate cur;drop table tb /*
China IE CO
*/