Inquire how to query the value of the continuous six Numbers in numeric fields have?-CodePudding

Excuse me, I have A table T1, there was A field is A field that is NUMBER type, length is 11, excuse me I how to query out what are the values in this field contains 6 Numbers like 123456234, 567 and so on? Database for the 11 g, using regular expressions is best, thank you!

CodePudding user response:

A simple function many

 create or replace function test_1101 (p_num in number, p_times number) in the return number is 
Temp number; 
Times the number; 
The begin 
For I in 0.. Loop length (p_num) 
If temp=substr (p_num, I, 1) - 1 then 
Times:=times + 1; 
Then the if times=p_times - 1 
return 1; 
end if; 
The else 
Times:=0; 
end if; 
Temp:=substr (p_num, I, 1); 

End loop; 
return 0; 
End test_1101;

The select test_1101 (' 123456 ', 6) from dual;

CodePudding user response:

reference 1st floor nayi_224 response:

use function simple many

 create or replace function test_1101 (p_num in number, p_times number) in the return number is 
Temp number; 
Times the number; 
The begin 
For I in 0.. Loop length (p_num) 
If temp=substr (p_num, I, 1) - 1 then 
Times:=times + 1; 
Then the if times=p_times - 1 
return 1; 
end if; 
The else 
Times:=0; 
end if; 
Temp:=substr (p_num, I, 1); 

End loop; 
return 0; 
End test_1101;

The select test_1101 (' 123456 ', 6) from dual;

thank you very much, if the field is varchar2 type? Direct change seems to be no

CodePudding user response:

Can process data directly, why even bother, try this one, can be directly executed,

Digital type
WITH
T1 AS (
SELECT 123456 N the FROM DUAL UNION ALL
SELECT 3456789 N the FROM DUAL UNION ALL
SELECT 3556789 N the FROM DUAL)
SELECT * FROM t. (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 & lt;=LENGTH (TO_CHAR (T1) N))) N1 FROM T1
) T WHERE T.N T.N=1;

The character type
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 '35 a56789' N FROM DUAL)
SELECT * FROM t. (SELECT T1. *, (SELECT SUM ((SUBSTR (T1), N, 1, 1) + LEVEL - 1) * POWER (10, LENGTH (T1) N) - LEVEL)) FROM DUAL
CONNECT BY LEVEL & lt;=LENGTH (T1) N)) N1 FROM T1
) T WHERE T.N=TO_CHAR (T.N 1);

CodePudding user response:

reference AHUA1001 reply: 3/f

can process data directly, why even bother, try this one, can be directly executed,

Digital type
WITH
T1 AS (
SELECT 123456 N the FROM DUAL UNION ALL
SELECT 3456789 N the FROM DUAL UNION ALL
SELECT 3556789 N the FROM DUAL)
SELECT * FROM t. (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 & lt;=LENGTH (TO_CHAR (T1) N))) N1 FROM T1
) T WHERE T.N T.N=1;

The character type
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 '35 a56789' N FROM DUAL)
SELECT * FROM t. (SELECT T1. *, (SELECT SUM ((SUBSTR (T1), N, 1, 1) + LEVEL - 1) * POWER (10, LENGTH (T1) N) - LEVEL)) FROM DUAL
CONNECT BY LEVEL & lt;=LENGTH (T1) N)) N1 FROM T1
) T WHERE T.N=TO_CHAR (T.N 1);

According to the question, this also should be six characters in a row, 9123456 and a123456 this string are direct error,

Pure SQL can solve, of course, is afraid of the building collapse

 with tab1 as (
Select id 1, 'a/0123456 b' num from dual union all 
Select 2 id, '/0123412345' the from dual 
) 
Tab2 as (
Select a t1. Id, 
Substr (t1) num, level 1) SRC, 
The case when the ASCII (substr (t1) num, level, 1)) between 48 and 57 
Then the ASCII (substr (t1) num, level, 1)) 
The else - 1 end assii, 
Level word 
The from tab1 t1 
Connect by level & lt;=length (t1) num) 
And the prior t1. Id=t1. Id 
And the prior sys_guid () is not null 
) 
, tab3 as (
Select a t1. Id, 
T1. SRC, 
T1 assii, 
T1. Word, 
Nullif (NVL (t1) assii - lag (t1. Assii) over (partition by t1. The id order by t1. The word), 1), 1) lg 
The from tab2 t1 
The order by t1. Id, t1. The word 
) 
, tab4, as (
Select a t1. *, 
The sum (t1), lg) over (partition by t1. The id order by t1. The word) sm 
The from tab3 t1 
) 
, tab5 as (
The select t1. Id, t1. Sm, count (1) 
The from tab4, t1 
Group by t1. Id, t1. Sm 
Having a count (1) & gt; 
=6) 
Select * from tab1 t1 where t1. Id in (select v1. Id from tab5 v1) 
;

So I said, with a simple function to varchar only need to add an if to go,