请问我有一张表T1,里面有一个字段是A字段,是NUMBER类型的,长度为11位,请问我如何查询出这个字段中有哪些值包含像123456,234567等等这样的6个数字?数据库为11g,使用正则表达式最好,谢谢!
解决方案 »
- 数据库设计---警察部门应该有哪些字段。
- 求PL SQL!
- 请问SQL有没有top 5函数?
- Oracle spatial数据读取
- oracle中是否有可將數字轉為二進制的涵數或方法!!!!
- 在线急求
- Select语句中的表名[table]是否可以通过外部调用时输入,而不必在语句中指定?
- 【求助】oracle 中的 ADDM 的问题
- 想在一个包(存储过程)中select v$session 和 v$sqltext
- 在C#中,循环用OleDbReader进行查询,一段时间后,出现:ORA-01000: 超出打开游标的最大数的ORACLE异常,不知如何解决。请高手解答。
- Oracle远程无法连接
- oracle如何对查询结果进行2次处理
create or replace function test_1101(p_num in number, p_times in number) return number is
temp number;
times number;
begin
for i in 0 .. length(p_num) loop
if temp = substr(p_num, i, 1) - 1 then
times := times + 1;
if times = p_times - 1 then
return 1;
end if;
else
times := 0;
end if;
temp := substr(p_num, i, 1);
end loop;
return 0;
end test_1101;
select test_1101('123456', 6) from dual;
WITH
T1 AS (
SELECT 123456 N FROM DUAL UNION ALL
SELECT 3456789 N FROM DUAL UNION ALL
SELECT 3556789 N FROM DUAL)
SELECT T.* FROM (SELECT T1.*,(SELECT SUM((SUBSTR(TO_CHAR(T1.N),1,1)+LEVEL-1) * POWER(10,LENGTH(TO_CHAR(T1.N))-LEVEL)) FROM DUAL
CONNECT BY LEVEL <= LENGTH(TO_CHAR(T1.N))) N1 FROM T1
) T WHERE T.N = T.N1 ;字符类型的
WITH
T1 AS (SELECT '123456' N FROM DUAL UNION ALL
SELECT '3456789' N FROM DUAL UNION ALL
SELECT '3556789' N FROM DUAL UNION ALL
SELECT '35A56789' N FROM DUAL)
SELECT T.* FROM (SELECT T1.*,(SELECT SUM((SUBSTR(T1.N,1,1)+LEVEL-1) * POWER(10,LENGTH(T1.N)-LEVEL)) FROM DUAL
CONNECT BY LEVEL <= LENGTH(T1.N)) N1 FROM T1
) T WHERE T.N = TO_CHAR(T.N1) ;
按照题意,9123456这种也应该算连续6个字符,而且a123456这种字符串都直接报错了。纯sql当然能解决,就是怕楼主崩溃
with tab1 as (
select 1 id, 'a/0123456b' num from dual union all
select 2 id, '/0123412345' from dual
)
, tab2 as (
select t1.id,
substr(t1.num, level, 1) src,
case when ascii(substr(t1.num, level, 1)) between 48 and 57
then ascii(substr(t1.num, level, 1))
else -1 end assii,
level ord
from tab1 t1
connect by level <= length(t1.num)
and prior t1.id = t1.id
and prior sys_guid() is not null
)
, tab3 as (
select t1.id,
t1.src,
t1.assii,
t1.ord,
nullif(nvl(t1.assii - lag(t1.assii) over(partition by t1.id order by t1.ord), -1), 1) lg
from tab2 t1
order by t1.id, t1.ord
)
, tab4 as (
select t1.*,
sum(t1.lg) over(partition by t1.id order by t1.ord) sm
from tab3 t1
)
, tab5 as (
select t1.id, t1.sm, count(1)
from tab4 t1
group by t1.id, t1.sm
having count(1) >= 6
)
select*from tab1 t1 where t1.id in (select v1.id from tab5 v1)
;所以我说用函数简单吗,改成varchar的只需要加一个if就行了。