請問我有一張表T1，里面有一個欄位是A欄位，是NUMBER型別的，長度為11位，請問我如何查詢出這個欄位中有哪些值包含像123456，234567等等這樣的6個數字？資料庫為11g，使用正則運算式最好，謝謝！
uj5u.com熱心網友回復：
用函式吧 簡單不少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;
uj5u.com熱心網友回復：
謝謝啦，如果欄位是varchar2型別的呢？直接改好像不行uj5u.com熱心網友回復：
能直接處理的資料，為啥還要那么麻煩，試試這個，直接執行即可。數字型別的
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) ;
uj5u.com熱心網友回復：
按照題意，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就行了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/26377.html
標籤：開發
下一篇：oracle序時賬