ORACLE里varchar类型的字段id都是数字比较大小
1
15
20
50
我要取id<100的就是上面的所有结果 如果直接这么写肯定只能取到1 因为是按位比较的 那么我该怎么写
最新回答
最初
2024-09-21 09:29:08
WHERE
CASE WHEN REGEXP_LIKE(id,'(^[+-]?\d{0,}\.?\d{0,}$)') THEN to_number(id)
ELSE NULL END < 100;
下面是详细的说明：
你可以选择仔细看看， 也可以选择 忽略掉。
某些情况下。
存在有一个表中的某一列， 数据类型是 varchar.
其中有可能存储数字， 也有可能存储文本信息。
而当查询的时候， 有时候需要按照 数字的方式来查询。
测试表/数据如下：
CREATE TABLE test_varchar_save_num(
id varchar(10)
);
INSERT INTO test_varchar_save_num VALUES( '1');
INSERT INTO test_varchar_save_num VALUES( '15');
INSERT INTO test_varchar_save_num VALUES( '20');
INSERT INTO test_varchar_save_num VALUES( '50');
INSERT INTO test_varchar_save_num VALUES( '1B');
INSERT INTO test_varchar_save_num VALUES( '2A');
INSERT INTO test_varchar_save_num VALUES( '1 ');
INSERT INTO test_varchar_save_num VALUES( '2 ');
INSERT INTO test_varchar_save_num VALUES( ' 1');
INSERT INTO test_varchar_save_num VALUES( ' 2');
INSERT INTO test_varchar_save_num VALUES( 'ABC');
要求查询 id 范围， 在 10~30 之间的数据.
无视数据类型的写法. （ORA-01722: 无效数字）
SELECT
*
FROM
test_varchar_save_num
WHERE
id BETWEEN 10 AND 30;
ERROR:
ORA-01722: 无效数字
数字参数修改为 varchar 的写法. （结果数据不正确）
SELECT
*
FROM
test_varchar_save_num
WHERE
id BETWEEN '10' AND '30';
ID
--------------------
15
20
1B
2A
2
使用正则表达式 + to_number 的写法（ORA-01722: 无效数字）
SELECT
*
FROM
test_varchar_save_num
WHERE
REGEXP_LIKE(id,'(^[+-]?\d{0,}\.?\d{0,}$)')
AND to_number(id) BETWEEN 10 AND 30;
ERROR:
ORA-01722: 无效数字
使用 正则表达式子查询 + 主查询 to_number 的写法（ORA-01722: 无效数字）
SELECT
*
FROM
(
SELECT
*
FROM
test_varchar_save_num
WHERE
REGEXP_LIKE(id,'(^[+-]?\d{0,}\.?\d{0,}$)')
) subQuery
WHERE
to_number(id) BETWEEN 10 AND 30;
ERROR:
ORA-01722: 无效数字
使用 case when ， 将非数字的，转化为 null 的写法 （完成处理）
SELECT
*
FROM
test_varchar_save_num
WHERE
CASE WHEN REGEXP_LIKE(id,'(^[+-]?\d{0,}\.?\d{0,}$)') THEN to_number(id)
ELSE NULL END BETWEEN 10 AND 30;
ID
--------------------
15
20
别闹~乖
2024-09-21 09:43:41
如以下语句：
create table test
(id varchar2(4001));执行时会报错。
如果将语句改为如下，则会执行成功。
create table test
(id varchar2(4000));关于oracle中的字符类型char、varchar、varchar2
的区别
区别:
1．char的长度是固定的，而varchar2的长度是可以变化的，
比如，存储字符串“abc"，对于char
(20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的varchar2
(20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。
2．char的效率比varchar2的效率稍高。
3．目前varchar是varchar2的同义词。工业标准的varchar类型可以存储空字符串，但是oracle不这样做，尽管它保留以后这样做的权利。oracle自己开发了一个数据类型varchar2，这个类型不是一个标准的varchar，它将在数据库中varchar列可以存储空字符串的特性改为存储null值。如果你想有向后兼容的能力，oracle建议使用varchar2而不是varchar。
快乐至上
2024-09-21 04:16:24
追问
报无效数字 你试过没
追答
无效数字是因为你那里里有存在不是数字的东西
---------------------------
以上是当插入的数据都是数字的字符时，大小都是按照正常数字来比较的
当我插入了一条非数字型字符
首先id<100无法直接进行比较，会报无效数字的错，因为当有非数字字符插入时，所有的数字的字符都不会隐性转成数字了，所以导致后边的大小比较时只有1被选出来
开始你也没说里边有非数字的字符，所以肯定用to_number就可以了
你这个貌似只能把非数字的字符都屏蔽掉，然后用to_number做比较
追问
不好意思 确实有条非数字的数据 那怎么办 我查了下to_number不能过滤啊
追答
查下那个非数字的字符，比如那个字符叫'a123'
where id'a123' and to_number(id)<100
追问
这个真的不行 我之前就试过了 to_number不能过滤有非数字的那列
追答
比如表名t，那个字符还是叫'a123'
select * from
(select * from t where id<>'a123') t1
where to_number(id)<100
这样真可以,我刚试了
热门标签
