那你先把是数字的筛选出来呗 select * from (select * from a where replace(translate(segment2, '0123456789', '0'), '0', '') IS NULL) t WHERE 1=1 AND t.segment2 >= to_number('800') AND t.segment2 <= to_number('2000')
WHERE 1=1 AND to_number(t.segment2) >= 800 AND to_number(t.segment2) <= 2000 AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit]]+$');
WHERE 1=1 AND to_number(t.segment2) >= 800 AND to_number(t.segment2) <= 2000 AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$');
阁下的思路很不错,我稍微修改了一下但是还是有问题 SELECT to_number(gcc.segment2) FROM gl_code_combinations gcc WHERE 1 = 1 AND replace(translate(gcc.segment2, '0123456789 ', '0'), '0', '') IS NULL --去掉所有非数字字符(不包括null) AND translate(gcc.segment2, '0123456789', '0') IS NOT NULL --去掉所有NULL的 ORDER BY to_number(gcc.segment2) 这里能查询到数据,并且能转成to_number显示出来 但是要想用到他还是不行 SELECT t.* from (SELECT to_number(gcc.segment2) a FROM gl_code_combinations gcc WHERE 1 = 1 AND replace(translate(gcc.segment2, '0123456789 ', '0'), '0', '') IS NULL --去掉所有非数字字符(不包括null) AND translate(gcc.segment2, '0123456789', '0') IS NOT NULL --去掉所有NULL的 ORDER BY to_number(gcc.segment2)) t WHERE 1 = 1 AND t.a <= 800还是会报 ORA-01772 无效数字 错误 这没道理啊,既然都能转成数字类型为什么不能(作为一个子查询)用呢?
WHERE 1=1 AND to_number(t.segment2) >= 800 AND to_number(t.segment2) <= 2000 AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$'); 没用啊还是报ORA-01772 无效数字 错误
WHERE 1=1 AND to_number(t.segment2) >= 800 AND to_number(t.segment2) <= 2000 AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$'); 没用啊还是报ORA-01772 无效数字 错误 先用这个条件取出只含数字的记录 REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$'); 再将只含数字的记录进行筛选 WHERE 1=1 AND to_number(t.segment2) >= 800 AND to_number(t.segment2) <= 2000
WHERE 1=1 AND to_number(t.segment2) >= 800 AND to_number(t.segment2) <= 2000 AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$'); 没用啊还是报ORA-01772 无效数字 错误 先用这个条件取出只含数字的记录 REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$'); 再将只含数字的记录进行筛选 WHERE 1=1 AND to_number(t.segment2) >= 800 AND to_number(t.segment2) <= 2000 select t.* from (select gcc.segment2 s from gl_code_combinations gcc WHERE 1 = 1 AND REGEXP_LIKE(gcc.SEGMENT2, '^[[:digit:]]+$')) t where 1 = 1 AND to_number(to_single_byte(t.s)) >= 800 AND to_number(to_single_byte(t.s)) <= 2000 终于解决了原来该死的数据表里面还有全角字符数字,真是无语啊,我加了to_single_byte()就没问题了 谢谢你了!
AND t.segment2 >= to_number('800')
AND t.segment2 <= to_number('2000')
AND to_number(t.segment2) >= 800
AND to_number(t.segment2) <= 2000
AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit]]+$');
AND to_number(t.segment2) >= 800
AND to_number(t.segment2) <= 2000
AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$');
阁下的思路很不错,我稍微修改了一下但是还是有问题
SELECT to_number(gcc.segment2)
FROM gl_code_combinations gcc
WHERE 1 = 1
AND replace(translate(gcc.segment2, '0123456789 ', '0'), '0', '') IS NULL --去掉所有非数字字符(不包括null)
AND translate(gcc.segment2, '0123456789', '0') IS NOT NULL --去掉所有NULL的
ORDER BY to_number(gcc.segment2)
这里能查询到数据,并且能转成to_number显示出来
但是要想用到他还是不行
SELECT t.* from
(SELECT to_number(gcc.segment2) a
FROM gl_code_combinations gcc
WHERE 1 = 1
AND replace(translate(gcc.segment2, '0123456789 ', '0'), '0', '') IS NULL --去掉所有非数字字符(不包括null)
AND translate(gcc.segment2, '0123456789', '0') IS NOT NULL --去掉所有NULL的
ORDER BY to_number(gcc.segment2)) t
WHERE 1 = 1
AND t.a <= 800还是会报 ORA-01772 无效数字 错误
这没道理啊,既然都能转成数字类型为什么不能(作为一个子查询)用呢?
AND to_number(t.segment2) >= 800
AND to_number(t.segment2) <= 2000
AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$');
没用啊还是报ORA-01772 无效数字 错误
AND to_number(t.segment2) >= 800
AND to_number(t.segment2) <= 2000
AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$');
没用啊还是报ORA-01772 无效数字 错误
先用这个条件取出只含数字的记录
REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$');
再将只含数字的记录进行筛选
WHERE 1=1
AND to_number(t.segment2) >= 800
AND to_number(t.segment2) <= 2000
AND to_number(t.segment2) >= 800
AND to_number(t.segment2) <= 2000
AND REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$');
没用啊还是报ORA-01772 无效数字 错误
先用这个条件取出只含数字的记录
REGEXP_LIKE(T.SEGMENT2,'^[[:digit:]]+$');
再将只含数字的记录进行筛选
WHERE 1=1
AND to_number(t.segment2) >= 800
AND to_number(t.segment2) <= 2000
select t.*
from (select gcc.segment2 s
from gl_code_combinations gcc
WHERE 1 = 1
AND REGEXP_LIKE(gcc.SEGMENT2, '^[[:digit:]]+$')) t
where 1 = 1
AND to_number(to_single_byte(t.s)) >= 800
AND to_number(to_single_byte(t.s)) <= 2000
终于解决了原来该死的数据表里面还有全角字符数字,真是无语啊,我加了to_single_byte()就没问题了
谢谢你了!
谢谢你了!
谢谢你了! 、、数据excel导的吧、、