ORACLE TRANSLATE 字符一对一转换

最新推荐文章于 2024-04-17 17:05:55 发布
原创 最新推荐文章于 2024-04-17 17:05:55 发布 · 1k 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#oracle #TRANSLATE

一、语法：
TRANSLATE(string,from_str,to_str)


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

三、允许使用的位置
过程性语句和SQL语句。
四、示例
Sql代码

1. SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;
2. TRANSLATE (
3. --------------
4. 123456ghij
5.
6. SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual;
7. TRANSL
8. ----------
9. 123456

语法：TRANSLATE(expr,from,to)

expr: 代表一串字符，from 与 to 是从左到右一一对应的关系，如果不能对应，则视为空值。

举例：

select translate('abcbbaadef','ba','#@') from dual　（b将被＃替代，a将被＠替代）

select translate('abcbbaadef','bad','#@') from dual　（b将被＃替代，a将被＠替代，d对应的值是空值，将被移走）

因此：结果依次为：@#c##@@def 和@#c##@@ef

Oracle查找非数字
tttzzzqqq2018的博客
07-26 1314
Oracle查找非数字
在ORACLE中找出某列非数字类型的数据
$Mr.zheng
04-10 2768
【代码】在ORACLE中找出某列非数字类型的数据。
is_number(oracle函数判断字段是否合法数字).sql
02-24
is_number(oracle函数判断字段是否合法数字)
Oracle中如何判断是否为数字函数
tianshibaijia
08-18 655
在Oracle中没有现成的判断是否为数字函数，下面我们就来介绍一下如何用三种方法来将其实现： 　　1. 利用 to_number 　　CREATE OR REPLACE FUNCTION is_numeric (str IN VARCHAR2) 　　RETURN NUMBER 　　IS 　　v_str FLOAT; 　　BEGIN 　　IF str IS NULL ...
Oracle查找非数字的字符
m0_46521990的博客
09-06 2459
Oracle查找非数字的字符
Oracle中的translate函数和replace函数的用法详解
12-15
- `translate`能够进行一对一的字符替换，并可以删除那些在`from_string`中有但`to_string`中没有的字符。 - `replace`则是替换整个子字符串，而不是单个字符，而且它不会删除字符，除非提供的`search_string`在`...
解决Oracle字符串中包含数字、特殊符号的排序问题
09-09
例如，如果我们有一个地址字符串，我们希望将数字转换为特定字符以便正确排序，可以这样做： ```sql SELECT TRANSLATE('1st Ave 10 Building 201 Room', '0123456789', '9999999999') AS "Sorted_Address" FROM ...
oracle translate
03-29
尽管 `REPLACE()` 函数也能达到类似的文本替换效果，但它仅支持一对一的整体子串替代；而 `TRANSLATE()` 则允许更灵活地定义单个字符级别的变换规则。因此，在涉及复杂模式或者批量调整需求的情况下，推荐优先考虑后...
oracle非数字合计，将字段中含有汉子的数据自动转换为 数字0
01-11
在Oracle数据库环境中，有时我们需要处理包含非数字字符的字段，并将其转换为数字进行计算或统计。根据提供的文件信息，本文将详细介绍如何实现“oracle非数字合计”，即将字段中含有汉字或其他非数字字符的数据自动...
Oracle 判断值是否为数字的函数
热门推荐
04-24 1万+
Oracle 判断值是否为数字的函数   Oracle PL/SQL并没有提供一个判断值是否为数字的函数，所以我们在实际中遇到以下的问题时， 常常会很郁闷。 例如有一个表， SQL> desc userlogin 名称                           是否为空?类型 ----------------------------------------- -----
Oracle自定义判断数据是否为数值函数
12-14
1 CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2) 2     RETURN NUMBER 3 IS 4     v_str VARCHAR2 (1000); 5 BEGIN 6     IF str IS NULL 7     THEN 8        RETURN 0; 9     ELSE 10        v_str := translate(str， '.0123456789'， '.'); 11 12        IF v_str = '.' OR v_str = '+.' OR v_str
oracel中判断字段是否为数字的函数
04-01
oracel中判断字段是否为数字的函数,查找非法字符解决多表关联报错
oracle几种查找非数字字符的方法
zhj330491369的博客
04-17 4160
演变1：TRANSLATE后配合replace函数：replace(translate(字段, '0123456789', '0'), '0', '') IS NOT NULL---通过replace把结果替换为空，最终筛选出不符合条件的记录。select * from 表 t where (NVL2(TRANSLATE(trim(字段), '\-1234567890.', '\'),'CHAR', 'NUMBER')) = 'CHAR';相反，去掉数字部分，保留非数字部分（表达式中去掉特殊符号。
oracle判断是否是数字函数,Oracle中判断字段是否为数字
weixin_39805087的博客
04-08 852
在我们平常的开发中可能会遇到这样的问题，就是判断某一列是否全部由数字组成，我们都知道oracle并没有给我们提供这样一个现成的函数，那么根据我的经验我总结了两个行之有效的方法(列名：column，表名：table)：1.使用trim+translate函数：复制代码 代码如下:select * from table where trim(translate(column,'0123456789',...
oracle中判断一个字符串是否全部为数字的函数
你们有病吗？
01-09 1797
作者：Rainny日期：2009-1-8创建测试表：create table test(c_id   varchar2(20));插入测试数据：insert into test values (1234); insert into test values (45678); insert into test values (-1234); insert int
oracle怎么判断值是否是数字,Oracle中如何判断是否为数字函数
weixin_33980357的博客
04-03 4787
许多用过ASP的朋友对isnumeric函数都有一定的了解，这是一个常用的判断入参是否为数字的函数。而在Oracle中没有现成的判断是否为数字函数，下面我们就来介绍一下如何用三种方法来将其实现：1. 利用 to_numberCREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)RETURN NUMBERISv_str FLOAT;BEGINIF...
oracle判断数值,oracle自定义判断数据是否为数值函数
weixin_42520580的博客
04-12 1636
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)RETURN NUMBERISv_str VARCHAR2 (1000);BEGINIF str IS NULLTHENRETURN 0;ELSEv_str := translate(str, '.0123456789', '.');IF v_str = '.' OR v_str = '+....
oracle函数-判断一个字段是否纯数字
u010966868的博客
11-28 1万+
拿到一个字符串，需要判断下是否纯数字组成 方法1： 这个方法是之前一个oracle群里的同学给写的。直接套用过来了 这个方法是使用exception create function F_IF_NUMBER(p_str in varchar2) return number as     e_error exception;     pragma
oracle 查找不是纯数字的记录
kfzx3427的博客
05-27 2704
select * from (select distinct translate(replace(id, '.', ''), '/1234567890', '/') col from xxx) where col is not null
oracle判断一个字符不为纯数字
最新发布
12-19
<think>我们之前讨论了如何判断一个字符串是否为数字，现在用户的问题是如何判断一个字符（字符串）不为纯数字。 根据用户最后一次提问，他想要的是“不为纯数字”的方法。我们可以通过取反之前的方法来实现。 根据之前的引用和讨论，我们可以采用以下几种方法： 方法1：使用TRANSLATE函数（取反） 之前判断纯数字的条件：`TRANSLATE(字符串, 'x0123456789.', 'x') IS NULL` 那么不为纯数字的条件就是：`TRANSLATE(字符串, 'x0123456789.', 'x') IS NOT NULL` 但是注意，这个方法会排除空字符串和NULL，所以如果用户希望NULL也视为非纯数字，那么可以加上OR条件（或者根据需求调整）。 方法2：使用正则表达式（取反） 之前判断纯数字的正则：`REGEXP_LIKE(字符串, '^[0-9]+(\.[0-9]*)?$')`（这里我们考虑小数，但是用户可能只需要整数？） 如果只考虑整数，那么正则表达式为：`^[0-9]+$` 那么不为纯数字（整数）的条件：`NOT REGEXP_LIKE(字符串, '^[0-9]+$')` 注意：如果需要考虑小数，则使用相应的正则表达式取反。 方法3：使用自定义函数（取反） 如果之前有一个函数`is_number`返回1表示是数字，0表示不是，那么我们可以用`is_number(字符串)=0`来判断不为纯数字。 但是用户的需求是“不为纯数字”，注意这里可能包含空字符串、NULL、含有非数字字符的字符串等。 根据引用[1]中给出的正则表达式，判断全数字（整数）的写法：`regexp_like(DM, '^[0-9]+$')`，那么不为全数字的写法就是： `NOT regexp_like(DM, '^[0-9]+$')` 但是用户可能要求的是不仅仅是整数，也可能包括小数？根据引用[3]中提到的方法，使用了小数点，所以我们需要明确用户的需求。 用户的问题描述是“判断一个字符不为纯数字”，这里“纯数字”可以理解为整数，也可以包括小数？从用户之前的提问中没有明确，但根据引用[3]中使用了小数点，我们可以考虑两种情况。 不过，用户最后一次问题中特别提到“参考站内引用”，其中引用[1]使用了整数判断，引用[3]使用了小数点。但用户现在的需求是“不为纯数字”，所以我们可以提供两种方案： 方案A：只判断整数（即字符串全部由数字组成，没有小数点） 方案B：判断数字（包括整数和小数，即可以有一个小数点） 我们分别给出： 1. 判断不为整数（纯数字整数）： ```sql SELECT * FROM your_table WHERE NOT REGEXP_LIKE(column_name, '^[0-9]+$') OR column_name IS NULL; -- 如果NULL也需要视为非纯数字，则加上OR条件。注意：NULL在Oracle中不会被正则表达式匹配到，所以NOT REGEXP_LIKE(NULL)的结果是NULL，不会被选中，因此需要显式处理NULL。 ``` 或者使用TRANSLATE（只考虑数字，不考虑小数点）： ```sql SELECT * FROM your_table WHERE TRANSLATE(column_name, 'x0123456789', 'x') IS NOT NULL OR column_name IS NULL; ``` 注意：这里去掉了小数点，因为我们只判断整数。 2. 判断不为数字（包括小数）： 使用正则表达式（考虑小数）： ```sql SELECT * FROM your_table WHERE NOT REGEXP_LIKE(column_name, '^[0-9]*\.?[0-9]*$') OR column_name IS NULL; ``` 注意：这个正则表达式允许小数点前和小数点后为空，所以可以匹配".1"或"1."，但可能并不符合要求（通常我们要求小数点前后至少有一个数字）。更严格的是：`'^[0-9]+(\.[0-9]*)?$'`或者`'^[0-9]*\.[0-9]+$'|'^[0-9]+\.?[0-9]*$'`等。这里我们采用严格一点的：整数和小数（小数点前后至少有一个数字），即： `'^[0-9]+(\.[0-9]*)?$'|'^\.[0-9]+$'` 或者更简洁的：`'^(\d+\.?\d*|\.\d+)$'`（注意在Oracle中，正则表达式中的\d不一定支持，建议用[0-9]）。 因此，我们可以写： ```sql SELECT * FROM your_table WHERE NOT REGEXP_LIKE(column_name, '^([0-9]+\.?[0-9]*|\.[0-9]+)$') OR column_name IS NULL; ``` 使用TRANSLATE方法判断包括小数的数字比较复杂，因为需要确保小数点只能出现一次。引用[3]的方法是： `ltrim(replace(字符串,'.'),'0123456789') is null and length(字符串)-length(replace(字符串,'.'))<=1` 那么取反就是： `ltrim(replace(字符串,'.'),'0123456789') is not null or length(字符串)-length(replace(字符串,'.'))>1` 或者考虑NULL的情况，但这样写比较复杂。 考虑到用户的需求是“不为纯数字”，而纯数字的定义可能是整数或小数，我们根据引用[1]的上下文（用户示例中都是整数）以及用户问题中“纯数字”的字面意思（通常指整数），我们优先提供整数的判断方法。 因此，我们给出以下两种情况的解决方案： 情况1：纯数字指整数（即字符串全部由数字0-9组成，没有小数点） 情况2：纯数字指数字（包括整数和小数，但只能有一个小数点，且小数点前后至少有一个数字） 我们分别给出方法： 情况1：不为整数（纯数字整数）的方法： 方法A（正则）： ```sql SELECT * FROM your_table WHERE NOT REGEXP_LIKE(column_name, '^[0-9]+$') OR column_name IS NULL; ``` 注意：如果原字符串为空字符串（''），那么正则表达式`^[0-9]+$`不匹配（因为至少一个数字），所以会被选中。NULL需要单独处理。 方法B（TRANSLATE）： ```sql SELECT * FROM your_table WHERE TRANSLATE(column_name, 'x0123456789', 'x') IS NOT NULL OR column_name IS NULL; ``` 注意：空字符串会被TRANSLATE函数转换为NULL（因为去掉数字后剩下空字符串，而空字符串在Oracle中被视为与NULL不同？需要测试）。实际上，空字符串在Oracle中会被处理为NULL。所以，空字符串在TRANSLATE后得到NULL，然后`IS NOT NULL`为假，所以不会选中。但是，我们要求不为纯数字，空字符串应该被视为非纯数字，所以我们需要加上： `OR column_name IS NULL` 会包括NULL，但空字符串在Oracle中就是NULL，所以也包括了。 情况2：不为数字（包括小数）的方法： 方法A（正则）： ```sql -- 匹配整数或小数（包括.1和1.，但通常我们要求小数点前后至少有一个数字，所以这里我们要求：整数部分（可选）和小数部分（可选），但至少有一个数字） -- 我们采用：整数部分（可选）和小数部分（可选），但整个字符串不能为空，且小数点最多一个，且至少有一个数字。 -- 正则表达式：`^[0-9]*\.?[0-9]+$` 或 `^[0-9]+\.?[0-9]*$`，但这样写不能匹配以小数点开头的情况（如.123），所以合并： -- `^([0-9]*\.[0-9]+|[0-9]+\.[0-9]*|[0-9]+)$` -- 简化：`^(\d*\.\d+|\d+\.\d*|\d+)$` 或者用非捕获分组：`^(?:\d*\.\d+|\d+\.\d*|\d+)$`，但在Oracle中，我们使用[0-9]并且不支持非捕获分组也没关系。 -- 但是为了简单，我们可以接受：整数（至少一个数字）、小数（整数部分至少一个数字，小数部分任意）、小数部分至少一个数字（整数部分可以没有） -- 所以：`^[0-9]*\.[0-9]+$`（小数部分至少一个数字，整数部分任意） 或 `^[0-9]+\.[0-9]*$`（整数部分至少一个数字，小数部分任意） 或 `^[0-9]+$`（纯整数） -- 合并：`^([0-9]*\.[0-9]+|[0-9]+\.[0-9]*|[0-9]+)$` -- 但是这样写不能匹配像“123.”这样的字符串，它会匹配到第二种情况（整数部分至少一个数字，小数部分0个）。同样，“.123”匹配第一种情况。 -- 那么不为数字的条件就是： SELECT * FROM your_table WHERE NOT REGEXP_LIKE(column_name, '^([0-9]*\.[0-9]+|[0-9]+\.[0-9]*|[0-9]+)$') OR column_name IS NULL; ``` 方法B（TRANSLATE结合其他条件）： 引用[3]的方法：`select 1 from dual where ltrim(replace(&a,'.'),'0123456789') is null and length(&a)-length(replace(&a,'.'))<=1;` 这个方法是判断数字（可以有一个小数点）的，那么不为数字的条件就是： ```sql SELECT * FROM your_table WHERE ltrim(replace(column_name, '.', ''), '0123456789') IS NOT NULL -- 去掉小数点后，去掉所有数字，剩下的不为空（说明有其他字符） OR length(column_name) - length(replace(column_name, '.', '')) > 1 -- 小数点出现超过一次 OR column_name IS NULL; ``` 但是，由于用户的问题描述是“判断一个字符不为纯数字”，且参考引用[1]中的例子（筛选非数字行），引用[1]使用的是整数判断。所以，我们优先给出整数情况下的方法。 综合以上，我们给出两种解决方案（整数和小数），并注明使用场景。 由于用户没有明确是否包含小数，我们提供两种解释下的方法。 另外，我们也可以考虑使用自定义函数，但用户可能希望直接用SQL语句。 总结回答： 在Oracle中判断一个字符串不为纯数字，有以下方法（根据纯数字的定义）： 1. 如果纯数字指整数（仅由0-9组成）： 方法一：使用正则表达式（推荐） ```sql SELECT * FROM your_table WHERE NOT REGEXP_LIKE(your_column, '^[0-9]+$') OR your_column IS NULL; ``` 方法二：使用TRANSLATE函数 ```sql SELECT * FROM your_table WHERE TRANSLATE(your_column, 'x0123456789', 'x') IS NOT NULL OR your_column IS NULL; ``` 2. 如果纯数字指数字（包括整数和小数，允许小数点，但只能有一个小数点，且小数点前后至少有一个数字）： 方法一：使用正则表达式 ```sql SELECT * FROM your_table WHERE NOT REGEXP_LIKE(your_column, '^([0-9]+\.[0-9]*|[0-9]*\.[0-9]+|[0-9]+)$') OR your_column IS NULL; ``` 方法二：使用TRANSLATE和LTRIM函数（引用[3]方法取反） ```sql SELECT * FROM your_table WHERE LTRIM(REPLACE(your_column, '.', ''), '0123456789') IS NOT NULL OR LENGTH(your_column) - LENGTH(REPLACE(your_column, '.', '')) > 1 OR your_column IS NULL; ``` 注意：以上方法中，`your_column IS NULL`条件用于将NULL值视为非纯数字。如果不需要包括NULL，可以去掉。 请根据实际需求选择合适的定义和方法。 另外，自定义函数方法（使用TO_NUMBER捕获异常）也可以用于判断是否为数字，然后取反。但是性能较差，不适用于大数据量。 示例：使用自定义函数（仅用于判断数字，包括各种格式，如科学计数法，但这里不展开）然后取反： ```sql -- 假设有函数is_number（如前所述，返回1为数字，0为非数字） SELECT * FROM your_table WHERE is_number(your_column) = 0; -- 该函数返回0包括NULL情况（取决于函数定义，如果函数中不对NULL处理，则NULL会进入异常返回0） ``` 但自定义函数需要提前创建，且可能影响性能。 因此，推荐使用正则表达式方法，简洁明了。 根据引用[1]中的例子，用户想要筛选非数字行，他们使用的是整数判断的正则表达式。因此，我们推断用户的需求可能是整数。所以我们给出整数判断的取反作为主要答案。 最终，我们给出整数判断的取反方法（使用正则表达式）作为推荐： ```sql -- 查询所有不为纯数字（整数）的行，包括NULL SELECT * FROM your_table WHERE NOT REGEXP_LIKE(your_column, '^[0-9]+$') OR your_column IS NULL; ``` 如果字符串为空字符串（在Oracle中等于NULL），那么`your_column IS NULL`会将其包括进来。 注意：如果列中有负数符号（-）或其他符号，上述方法都不包括。如果需求包括负数，需要另外处理。 由于用户问题中“纯数字”通常指正整数（无符号），所以不考虑负数。 如果用户需要更复杂的数字格式，请根据实际调整正则表达式。 相关问题： 1. 如何判断一个字符串是否为数字（包括小数和负数）？ 2. 在Oracle中，如何筛选出包含非数字字符的行？ 3. 如何使用Oracle正则表达式匹配科学计数法表示的数字？ </think> 在Oracle中判断一个字符串**不为纯数字**（即不全是0-9的数字字符），可以通过以下方法实现。根据需求复杂度，提供三种方案： --- ### 方案1：基础方法（仅判断整数） 使用`TRANSLATE`函数移除非数字字符后判断是否存留内容： ```sql SELECT * FROM your_table WHERE TRANSLATE(your_column, 'x0123456789', 'x') IS NOT NULL OR your_column IS NULL; -- 包含NULL值[^1][^2] ``` **说明**： 1. `TRANSLATE(your_column, 'x0123456789', 'x')` 移除了所有数字字符 2. 结果`IS NOT NULL` 表示存在非数字字符 3. `OR your_column IS NULL` 将NULL值视为非数字 --- ### 方案2：正则表达式（推荐） 使用`REGEXP_LIKE`取反匹配纯数字模式： ```sql -- 判断非整数（严格模式） SELECT * FROM your_table WHERE NOT REGEXP_LIKE(your_column, '^[0-9]+$') OR your_column IS NULL; -- 判断非数字（包含小数） SELECT * FROM your_table WHERE NOT REGEXP_LIKE(your_column, '^[0-9]*\.?[0-9]+$') OR your_column IS NULL; ``` **正则说明**： - `^[0-9]+$`：纯整数匹配 - `^[0-9]*\.?[0-9]+$`：匹配整数、小数（如`123`, `.5`, `7.89`） - `NOT REGEXP_LIKE`对匹配结果取反 --- ### 方案3：处理复杂格式（含科学计数法/千分位） 使用正则表达式适配扩展格式： ```sql -- 匹配科学计数法 (如 1.23E+5) WHERE NOT REGEXP_LIKE(your_column, '^[+-]?\d+(\.\d+)?[Ee][+-]?\d+$') -- 匹配千分位数字 (如 1,000.25) WHERE NOT REGEXP_LIKE(your_column, '^[+-]?\d{1,3}(,\d{3})*(\.\d+)?$')[^3] ``` --- ### 示例数据验证 ```sql WITH samples AS ( SELECT '123' str FROM DUAL UNION ALL -- 纯数字 SELECT '12.3' FROM DUAL UNION ALL -- 小数 SELECT 'A123' FROM DUAL UNION ALL -- 含字母 SELECT '1,000' FROM DUAL UNION ALL -- 千分位 SELECT NULL FROM DUAL -- NULL值 ) SELECT str, CASE WHEN TRANSLATE(str, 'x0123456789', 'x') IS NOT NULL THEN '非纯数字' ELSE '纯数字' END AS result FROM samples; ``` **输出**： | STR | RESULT | |-------|------------| | 123 | 纯数字 | | 12.3 | 非纯数字* | | A123 | 非纯数字 | | 1,000 | 非纯数字 | | NULL | 非纯数字 | > *小数点在基础方案中被视为非数字字符，需用方案2/3处理 --- ### 使用建议 | 场景 | 推荐方法 | |--------------------------|------------------| | 简单整数判断 | `TRANSLATE` | | 精确数字格式（含小数） | 正则表达式 | | 科学计数法/千分位 | 扩展正则表达式 | | 高性能要求 | 避免`TO_NUMBER`异常捕获[^2]
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值