CREATE FUNCTION [dbo].[Convert_Str] (@Str nvarchar(4000),@ToBIG bit) --------------@Str:要轉換的字符串.ToBIG 转换标志,为1,表示 GB-->BIG,否则是 BIG-->GB
RETURNS nvarchar(4000) AS
BEGIN
declare @returnStr varchar(4000),@index int,@repChar nchar(1),@indexChar nchar(1) --@Str字符串對應的每個字符
set @returnStr=''
set @index = 1
--循環取得轉換字符串的每個值
while @index <= Len(@str)
begin
set @repChar = '' --- 記得每次循環前要置@repChar為空
set @indexChar = SubString(@Str,@index,1)
/*------------------------------------------得出取個字符對應的轉換字符---------------------------------------------------------*/
if @toBIG=1 --轉為繁體
select @repChar=IsNull(replace(@indexChar,GBField,BIGField) ,@indexChar)
from CodeConvert_ccv
where GBField = @indexChar
else
select @repChar=IsNull(replace(@indexChar,BIGField,GBField),@indexChar) --轉為簡體
from CodeConvert_ccv
where BIGField = @indexChar
/*---------------------------------------------------------- 取字符結束-------------------------------------------------------------------------*/
set @index = @index+1
if @repChar=''
set @returnStr = @returnStr + @indexChar
else
set @returnStr = @returnStr + @repChar
end
return (@returnStr)
END
