ORACLE中TRANSLATE函数的实际运用

在实际工作中，我们多少会遇到【ORA-01722: invalid number】这个错误，这是属于类型不匹配造成的。比如当字段中包含了字符时，我们使用TO_NUMBER函数进行类型转换就会提示这个错误。这时就可以使用TRANSLATE函数进行纯数字过滤或者查找包含字符的数据。

TRANSLATE函数

语法

translate(string,from_str,to_str)

解析
translate函数在执行过程中首先遍历string字符串，检查其中每个字符在from_str字符串中是否有匹配的字符，如果有则获取在from_str中第一次出现位置，最后返回to_str中同样位置的字符。如果from_str中没有匹配上，则返回string中原始的字符。如果from_str匹配上了，但是to_str中没有对应位置点的字符，则直接删除字符串。

注意： to_str不能是null或者’'，否则会返回空值，translate也不能用于CLOB。

用例一
string字符串中的字符在from_str中都能匹配，from_str中的位置点在to_str都能匹配。

--查询
select translate('abg', 'aabcdefg', '12345678') as test from dual;
--输出
138
--解读
translate函数首先遍历字符串'abg'。
其中第一个字符'a'在'aabcdefg'中第一次出现的位置是1，于是获取'12345678'中第一个字符，也是就是1。
其中第二个字符'b'在'aabcdefg'中第一次出现的位置是3，于是获取'12345678'中第三个字符，也是就是3。
其中第三个字符'g'在'aabcdefg'中第一次出现的位置是8，于是获取'12345678'中第八个字符，也是就是8。
最后组合起来就是结果138。

用例二：
string字符串中的字符在from_str中部分匹配，from_str中的位置点在to_str都能匹配。

--查询
select translate('ab测试g', 'aabcdefg', '12345678') as test from dual;
--输出
13测试8
--解读
translate函数在遍历'ab测试g'字符串时'测试'字符在'aabcdefg'串中没有成功匹配，于是不进行翻译，原样返回。
所以最后的结果是'13测试8'.

用例三：
string字符串中的字符在from_str中部分匹配，from_str中的位置点在to_str部分匹配。

--查询
select translate('ab测试g', 'aabcdefg', '1234567') as test from dual;
--输出
13测试
--解读
translate函数在遍历'ab测试g'字符串时'测试'字符在'aabcdefg'串中没有成功匹配，于是不进行翻译，原样返回。
字符'g'的位置点是8，在字符串中'1234567'没有位置点8，于是直接返回时直接删除字符'g'。
所以最后的结果是'13测试'.

实例一：
筛选translate_test表中cost 字段不是由纯数字组成的列。

--创建测试环境
create table translate_test(id number(20),cost varchar(30));
insert into translate_test values(1,'111');
insert into translate_test values(2,'222');
insert into translate_test values(3,'333test');
insert into translate_test values(4,'444测试');
commit;

数据如下

	1	111
	2	222
	3	333test
	4	444测试

查询数据

select * from translate_test t where trim(translate(t.cost,'0123456789',' ')) is not null

输出结果

3	333test
4	444测试