Geek Answers Handbook

Regex: define any substring of the alphabet?

First step

  • With an "alphabet" defined as ABCDEFGHIJKLMNOPQRSTUVWXYZ, I want to find any substring of the alphabet. I need to build more from here, but this is my first task.

final goal

  • Given a character pattern (AZ) without repetitions and spaces, and only character expansion (ABDE, never ABED), replace all missing characters in the alphabet with a single space in the Oracle statement. Thus, the row column can read ABCDEGHIJKLMNOPQTUVWXYZ(F and RS are absent), and it needs to read ABCDE GHIJKLMNOPQ TUVWXYZ.

Is it possible?

David

+4
source share
5 answers

connect-by; 26 , . ASCII , ascii() 1-26 . :

var str varchar2(26);
exec :str := 'ABCDFGZ';

with alphabet as (
  select level as pos
  from dual connect by level <= 26
),
chars as (
  select substr(:str, level, 1) character,
    ascii(substr(:str, level, 1)) - 64 as pos
  from dual connect by level <= length(:str)
)
select listagg(nvl(chars.character, ' '))
  within group (order by alphabet.pos) as result
from alphabet
left outer join chars on chars.pos = alphabet.pos;

RESULT                   
--------------------------
ABCD FG                  Z

SQL * Plus, , .

, . ( , ) , ( , ). , .

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

select * from v42 :

        ID STR                        RESULT                   
---------- -------------------------- --------------------------
         1 A                          A                          
         2 Z                                                   Z 
         3 AZ                         A                        Z 
         4 ABCDFGZ                    ABCD FG                  Z 
         5 ABCDEGHIJKLMNOPQTUVWXYZ    ABCDE GHIJKLMNOPQ  TUVWXYZ

- SQL Fiddle.

CTE. , . , ...

CTE, :

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

(SQL Fiddle , " SQL".)

+3

.

WITH FULL_ALPHABETS AS
  (
    SELECT CHR(64+level) alpha,rownum AS id
      FROM DUAL
    CONNECT BY LEVEL<=26
  ),
INPUT_ALPHABETS AS
  (
    SELECT SUBSTR(UPPER('ABCDEFYZ'),level,1) alpha, rownum AS id
      FROM dual
    CONNECT BY level <= LENGTH(UPPER('ABCDEFYZ'))
  )
SELECT LISTAGG(NVL(I.ALPHA,' ')) WITHIN GROUP (ORDER BY F.ALPHA)
  FROM FULL_ALPHABETS F
    LEFT OUTER JOIN INPUT_ALPHABETS I
     ON (F.ALPHA = I.ALPHA)
ORDER BY F.ALPHA;
+3

: Oracle, .

:

  • ABCDEFGHIJKLMNOPQRSTUVWXYZ .

  • (?!([A-Z]+)(?=.*\1))[A-Z] ( dotall, )

- regex

+2

I am currently working with 10g, so I don't have LISTAGG. So far, my approach is similar to others, but I came up with this. I should mention that WM_CONCAT is not supported by Oracle if this is something that bothers you.

select replace(wm_concat(OUTPUT_CHAR),',') OUTPUT_STRING 
from
  (select nvl(INPUT_STRING.INPUT_CHAR,' ') OUTPUT_CHAR
   from 
    (select chr(64 + level) LETTER 
     from dual connect by level <= 26) ALPHABET
   left join 
    (select substr(:input_string, level, 1) INPUT_CHAR 
     from dual connect by level <= length(:input_string)) INPUT_STRING
   on ALPHABET.LETTER = INPUT_STRING.INPUT_CHAR
   order by ALPHABET.LETTER);
+1
source

No need to split and group strings. Just just regex_replace will work.

This line is surrounded [^and ], therefore, any characters that are not in this list will be replaced.

SQL> var str varchar2(26);
SQL> exec :str := 'AQ';

SQL> select regexp_replace(
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
  '[^'||:str||']'
  ,' '
)                               resulting_str
from dual;

RESULTING_STR
------------------------------
A               Q


SQL> exec :str := 'A';

select regexp_replace(
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
  '[^'||:str||']'
  ,' '
)                               resulting_str
from dual;

RESULTING_STR
------------------------------
A


SQL> exec :str := 'Z';

SQL> select regexp_replace(
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
  '[^'||:str||']'
  ,' '
)                               resulting_str
from dual;

RESULTING_STR
------------------------------
                         Z
0
source

More articles:


All Articles