首先先介绍 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 行受影响)