oracle - Regex: Detect any substring of the alphabet? -

first step

• with "the alphabet" defined abcdefghijklmnopqrstuvwxyz, want find substring of alphabet. need build more here, first challenge.

end-goal

• given pattern of characters (a-z) no repetitions , no whitespace , incrementing characters (abde, never abed), replace missing characters in alphabet single space within oracle statement. so, column of row might read abcdeghijklmnopqtuvwxyz (f , rs missing) , needs read abcde ghijklmnopq tuvwxyz.

is possible?

david

for single value can use 2 connect-by clauses; 1 generate 26 values, other split original string individual characters. since ascii codes consecutive, ascii() function can used generate number 1-26 each of characters present. left-join 2 lists:

var str varchar2(26); exec :str := 'abcdfgz';  alphabet (   select level pos   dual connect level <= 26 ), chars (   select substr(:str, level, 1) character,     ascii(substr(:str, level, 1)) - 64 pos   dual connect level <= length(:str) ) select listagg(nvl(chars.character, ' '))   within group (order alphabet.pos) result alphabet left outer join chars on chars.pos = alphabet.pos;  result                    -------------------------- abcd fg                  z  

this sql*plus bind variable avoid repeating string, plugged in somewhere else.

it's bit more complicated create view multiple rows table can cause problems connect-by. list of possible values has include primary (or unique, @ least) key table, , original string if want include (and other columns want table). split list needs include primary key, , needs included in outer join.

create view v42 possible (   select id, str, level pos   t42   connect level <= 26   , prior id = id   , prior sys_guid() not null ), actual (   select id, substr(str, level, 1) character,     ascii(substr(str, level, 1)) - 64 pos   t42   connect level <= length(str)   , prior id = id   , prior sys_guid() not null ) select possible.id, possible.str, listagg(nvl(actual.character, ' '))   within group (order possible.pos) result possible left outer join actual on actual.id = possible.id , actual.pos = possible.pos group possible.id, possible.str; 

then sample data, select * v42 gives:

        id str                        result                    ---------- -------------------------- --------------------------          1                                                             2 z                                                   z           3 az                                                z           4 abcdfgz                    abcd fg                  z           5 abcdeghijklmnopqtuvwxyz    abcde ghijklmnopq  tuvwxyz  

sql fiddle demo.

it might little cleaner recursive cte instead. or function works on 1 value @ time. or regex, of course...

here's recursive cte version, fun:

create view v42 possible(id, str, pos, character) (   select id, str, 1, 'a'   t42   union   select id, str, pos + 1, chr(64 + pos + 1)   possible   pos < 26 ), actual (id, str, pos, character) (   select id, str, 1, substr(str, 1, 1)   t42   union   select id, str, pos + 1, substr(str, pos + 1, 1)   actual   pos < length(str) ) select possible.id, possible.str, listagg(nvl(actual.character, ' '))   within group (order possible.pos) result possible left outer join actual on actual.id = possible.id , actual.character = possible.character group possible.id, possible.str; 

(sql fiddle odd things spacing, view plaintext output, 'run sql' drop-down.)