Oracle 里面的 TRANSLATE 以及 SQL Server 下的实现

首先先介绍 Oracle 里面的 TRANSLATE 的用法

先是语法：

Oracle

语法：TRANSLATE(char, from, to)

用法：返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。

若from比to字符串长，那么在from中比to中多出的字符将会被删除。

三个参数中有一个是空，返回值也将是空值。

 

 

SQL> SELECT TRANSLATE('2KRW229',

  2  　　'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

  3  　　'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') AS "License"

  4  　　FROM DUAL;

 

License

-------

9XXX999

 

某些情况下，要从一个字符串中，提取数字信息的，可以使用：

SQL> SELECT TRANSLATE('2KRW229',

  2  　　'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')

  3  　　AS "Translate example"

  4  　　FROM DUAL;

 

Tran

----

2229

 

更简单的写法，就是第一个字母是不存在的字母，后面全部是需要被删除的。

SQL> SELECT TRANSLATE('2KRW229',

  2  　　'#ABCDEFGHIJKLMNOPQRSTUVWXYZ', '#')

  3  　　AS "Translate example"

  4  　　FROM DUAL;

 

Tran

----

2229

 

还有一种写法，就是把需要保留的都标记下来，其他的都替换为空白

SQL> SELECT TRANSLATE('2KRW229',

  2      '1234567890' || '2KRW229',  '1234567890')

  3      AS "Translate example"

  4  FROM DUAL;

 

Tran

----

2229

 

下面是 SQL Server 下面的实现：

 

CREATE FUNCTION TRANSLATE(
 @string VARCHAR(MAX),
 @from_str VARCHAR(MAX),
 @to_str VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

  -- 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。
  -- TRANSLATE 是 REPLACE 所提供的功能的一个超集。
  -- 如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。
  -- to_str 不能为空。
  -- Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。
  IF @string IS NULL OR @from_str IS NULL OR @to_str IS NULL
  BEGIN
    RETURN NULL;
  END;

  -- 源长度 与 目标长度
  DECLARE @FromLen INT, @ToLen INT;
  SET @FromLen = LEN(@from_str);
  SET @ToLen = LEN(@to_str);

  -- 准备用于返回的数值.
  DECLARE @resultVal VARCHAR(MAX);
  SET @resultVal = @string;
  
  -- 用于存储 本次需要替换的字符信息.
  DECLARE @thisTimeReplace CHAR(1);

  
  -- 从后向前依次替换.
  WHILE @FromLen > 0
  BEGIN
    
    -- 取得本次即将要替换的字符.
    SET @thisTimeReplace = SUBSTRING(@from_str, @FromLen, 1);
    
    IF CHARINDEX(@thisTimeReplace, @from_str) < @FromLen
    BEGIN
      -- 假如当前这个要替换的字符，在前面还有，那么这里就不替换了
      -- 原因，为了支持 
      -- SELECT TRANSLATE('2KRW229', '1234567890' || '2KRW229',  '1234567890')
      -- 这样的效果.
      
      -- 向前处理上一个.
      SET @FromLen = @FromLen - 1;
      CONTINUE;
    END

    IF @FromLen > @ToLen
    BEGIN
      -- from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。
      SET @resultVal = REPLACE(@resultVal, SUBSTRING(@from_str, @FromLen, 1), '');
    END
    ELSE
    BEGIN
      -- from_str中的每个字符替换为to_str中的相应字符以后的string
      SET @resultVal = REPLACE(@resultVal, @thisTimeReplace, SUBSTRING(@to_str, @FromLen, 1));
    END;
    
    -- 处理完当前字符后，向前处理上一个.
    SET @FromLen = @FromLen - 1;
  END;
  
  -- 依次处理完毕后，返回结果.
  RETURN @resultVal;
  
END
go

 

 

测试结果：

 

1> SELECT dbo.TRANSLATE('2KRW229',

2>     '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

3>     '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') AS "License"

4> go

License

----------------

9XXX999

(1行受影响)

 

1>

2> SELECT dbo.TRANSLATE('2KRW229',

3>     '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')

4>     AS "Translate example"

5> go

Translate example

----------------

2229

(1行受影响)

 

1>

2>

3> SELECT dbo.TRANSLATE('2KRW229',

4>     '#ABCDEFGHIJKLMNOPQRSTUVWXYZ', '#')

5>     AS "Translate example"

6> go

Translate example

----------------

2229

(1行受影响)

 

1>

2> SELECT dbo.TRANSLATE('2KRW229',

3>     '1234567890' + '2KRW229',  '1234567890')

4>     AS "Translate example"

5> go

Translate example

----------------

2229

(1 行受影响)

microsoft 官网给的translate函数定义：

USE AdventureWorks
GO


IF OBJECT_ID (N'dbo.Translate', N'FN') IS NOT NULL
    DROP FUNCTION dbo.Translate;
GO
CREATE FUNCTION dbo.Translate (@Data VARCHAR(MAX), @DataToReplace VARCHAR(100), @ReplacedWithData VARCHAR(100))
RETURNS VARCHAR(MAX)


BEGIN

DECLARE @TranslaedData VARCHAR(MAX)

;WITH CTE(PosToReplace,Data,DataToReplace,ReplacedWithData) AS 
(
SELECT 1,CAST(@Data AS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,1,1) AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,1,1) AS VARCHAR(MAX)) AS ReplacedWithData
UNION ALL
SELECT C.PosToReplace+1 AS PosToReplace , CAST(REPLACE(C.Data,C.DataToReplace,C.ReplacedWithData) AS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,PosToReplace+1,1) AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,PosToReplace+1,1) AS VARCHAR(MAX)) AS ReplacedWithData
FROM CTE C
WHERE C.PosToReplace <= LEN(@DataToReplace)
)
SELECT  @TranslaedData = C.Data FROM CTE C WHERE C.PosToReplace = LEN(@DataToReplace)+1

RETURN @TranslaedData

END

但是我使用 100,000 条数据测试，结果证明，第一种方法比第二种方法快三倍。

http://hi.baidu.com/wangzhiqing99/item/2c577648c3446be5a4c06626

https://gallery.technet.microsoft.com/scriptcenter/Translate-Function-00f04329