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 行受影响)