How to find unknown repeated substring in string or table column

Question

I have a big string of numbers that is the input of the program (I supposed that the sequence name is 'S') it can be either a string or in a table column. the input string (S) produced by the table like table 1.
I want to find unknown sub strings that made the "S" string. each sub string made of 100 numbers.

We simplify the example like below: Each sub string contain three number instead of 100 number .

table 1:

table 1 description: each row of table 1 is belong to one of the sub string that repeated in "s" sting. For each column we have exactly ten numbers that this numbers made by random repeating of numbers belong to that column. For example: For the first column we can have for instance four 11 and six 65 or one 11 and nine 65 and etc. and other columns are exactly like that. And for columns with one number each ten repeating is that number for example column number seven is like below: 50 50 50 50 50 50 50 50 50 50 .

the output is repeated sub strings that made the "S" string. output:

11,10,13
30,40,50
65,66,61

can anyone help me with the pl_sql or java to solve the problem.

Try code from this article: http://stackoverflow.com/questions/10286677/how-to-check-for-repeating-sequence-in-an-integer — Michał M, Jun 28 '16 at 06:49
You need to clarify this question: How to determine in wich row a number belongs,F.i. how to determine that the 50 you highlighted has an empty value above and below. Give as much Info as you can, this is not clear enough — hendrik_at_geislersoftware, Jul 01 '16 at 11:40

score 0 · Answer 1 · answered Nov 17 '16 at 12:43

Here's some PL/SQL that I believe solves the basic problem as you have described. It will extend to your larger dataset I think but I had to make a few assumptions about splitting the numbers into groups. The basic process works for the data, description and result you have given.

SET SERVEROUTPUT ON;

DECLARE
    TYPE numbers_t IS TABLE OF INT(2);
    TYPE output_t  IS TABLE OF VARCHAR2(100);

    c_example VARCHAR2(160) := '1111111111111165656510101030306666666666134061134061616161611030303030306666666613131313134040404061505050505050505050506565656565656565656511111166666666666666';
    c_place   INT := 1;
    c_chunk   INT := 1;
    l_numbers numbers_t := numbers_t();
    l_output  output_t  := output_t();
    n binary_integer;

BEGIN
    l_output.EXTEND(3);
    WHILE c_place <= LENGTH(c_example)
    LOOP
        l_numbers.EXTEND(10);
        WHILE c_chunk <= 10
        LOOP
            l_numbers(c_chunk) := SUBSTR(c_example, c_place, 2);
            c_place := c_place + 2;
            c_chunk := c_chunk + 1;
        END LOOP;
        c_chunk := 1;
        l_numbers := SET(l_numbers);
        n := l_numbers.FIRST;
        WHILE (n IS NOT NULL)
        LOOP
                CASE
                WHEN l_numbers(n) < 30 THEN l_output(1) := l_output(1) || ',' || l_numbers(n);
                WHEN l_numbers(n) > 50 THEN l_output(3) := l_output(3) || ',' || l_numbers(n);
                ELSE l_output(2) := l_output(2) || ',' || l_numbers(n);
            END CASE;
            n := l_numbers.NEXT(n);
        END LOOP;
        l_numbers.DELETE();
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(TRIM(LEADING ',' FROM l_output(1)));
    DBMS_OUTPUT.PUT_LINE(TRIM(LEADING ',' FROM l_output(2)));
    DBMS_OUTPUT.PUT_LINE(TRIM(LEADING ',' FROM l_output(3)));
END;
/

How to find unknown repeated substring in string or table column

1 Answers1