sql自定义替换函数

[liao108]

能否在sql自定义有以下功能的函数？如何实现？

用指定字符串中的每个单词到以下数据表的'简写'中查询，如果存在，就有‘全名’中的值来代替。例如

I am from CN, not JP 在转换以后应该是I am from China, not Japan

数据表

简写 全名

CN China
JP Japan

[--小F--]

强大

[bancxc]

repalce

[Mirana_NightShade]

UP

[ACMAIN_CHM]

select replace(replace('I am from CN, not JP','CN','China'),'JP','Japan')

[dawugui]

create table tb(col1 varchar(20),col2 varchar(20))
insert into tb values('CN' ,   'China') 
insert into tb values('JP' ,   'Japan')
go
create proc my_proc @col as varchar(50) , @return as varchar(50) OUTPUT
as
begin
  set @col = @col + ' '
  declare @col3 as varchar(50)
  declare @col4 as varchar(50)
  set @return = ''
  set @col3 = ''
  set @col4 = ''
  while charindex(' ' , @col) > 0
  begin
    set @col4 = ''
    set @col3 = left(@col , charindex(' ' , @col) - 1) 
    select @col4 = col2 from tb where ' ' + col1 + ' ' = ' ' + @col3 + ' '
    if @col4 is not null and @col4 <> ''
       set @return = @return + @col4 + ' '
    else
       set @return = @return + @col3 + ' '
    set @col = substring(@col , charindex(' ' , @col) + 1, len(@col))
  end
end
go

declare @return as varchar(50)
set @return = ''

exec my_proc 'I am from CN , not JP' , @return OUTPUT
select @return
/*
                                                   
-------------------------------------------------- 
I am from China , not Japan 

（所影响的行数为 1 行）
*/

exec my_proc 'I am from JP , not CN' , @return OUTPUT
select @return
/*
                                                   
-------------------------------------------------- 
I am from Japan , not China 

（所影响的行数为 1 行）
*/

drop table tb
drop proc my_proc

 

[dawugui]

使用存储过程完成，在上述基础上稍微改了一下．

create table tb(col1 varchar(20),col2 varchar(20))
insert into tb values('CN' ,   'China') 
insert into tb values('JP' ,   'Japan')
go
create proc my_proc @col as varchar(50) , @return as varchar(50) OUTPUT
as
begin
  set @col = @col + ' '
  declare @col3 as varchar(50)
  declare @col4 as varchar(50)
  set @return = ''
  set @col3 = ''
  set @col4 = ''
  while charindex(' ' , @col) > 0
  begin
    set @col4 = ''
    set @col3 = left(@col , charindex(' ' , @col) - 1) 
    if right(@col3 , 1) <> ','
       select @col4 = col2 from tb where ' ' + col1 + ' ' = ' ' + @col3 + ' '
    else
       select @col4 = col2 + ',' from tb where ',' + col1 + ',' = ',' + @col3
    if @col4 is not null and @col4 <> ''
       set @return = @return + @col4 + ' '
    else
       set @return = @return + @col3 + ' '
    set @col = substring(@col , charindex(' ' , @col) + 1, len(@col))
  end
end
go

declare @return as varchar(50)
set @return = ''

exec my_proc 'I am from CN, not JP' , @return OUTPUT
select @return
/*

-------------------------------------------------- 
I am from China, not Japan 

（所影响的行数为 1 行）
*/

exec my_proc 'I am from CN , not JP' , @return OUTPUT
select @return
/*
                                                   
-------------------------------------------------- 
I am from China , not Japan 

（所影响的行数为 1 行）
*/

drop table tb
drop proc my_proc