All geek questions in one place

Oracle SQL Get last row in parentheses (may also contain parentheses)

I am using this query:

SELECT strain.id, TRIM(SUBSTR(strain.name, 1, INSTR(strain.name, '[')-1)) AS name
FROM species_strain strain

The above query gives me something like the following:

id    name
-----------------------------------------------
100   CfwHE3 (HH3d) Jt1 (CD-1)
101   4GSdg-3t 22sfG/J (mdx (fq) KO)
102   Yf7mMjfel 7(tm1) (SCID)
103   B29fj;jfos x11 (tmos (line x11))
104   B29;CD (Atm (line G5))
105   Ifkso30 jel-3
106   13GupSip (te3x) Blhas/J           --------> I don't want to get (te3x)

I need a regular expression that would give me the contents of the last set of parentheses (in which MAY OR CANNOT include 1 or more sets of parentheses inside) - this should be at the end of the line. If it is in the middle of the line, I do not want this.

I want to get the following:

(CD-1)
(mdx (fq) KO)
(SCID)
(tmos (line x11))
(Atm (line G5))

So, if I copy and paste my entire query, I have this, but that doesn't take into account the parentheses inside:

SELECT DISTINCT REGEXP_SUBSTR(strain.name, '\(.*?\)', 1, REGEXP_COUNT(strain.name, '\(.*?\)')) AS name
FROM (
  SELECT strain.id, TRIM(SUBSTR(strain.name, 1, INSTR(strain.name, '[')-1)) AS name
  FROM species_strain strain
) strain
WHERE INSTR(strain.name, '(', 1, 1) > 0

The query somehow works, but if I get a different set of brackets inside the main one, it breaks and I lose some data. It returns something like:

(CD-1)
(mdx (fq)          ---------> missing KO)
(SCID)
(tmos (line x11)   ---------> missing )
(Atm (line G5)     ---------> missing )

Additional requirements

, , , . , . .

+4
5

SQL ( /); " "; null , null , ( , ).

, "", - , . OP.

. , , id = 156, , "" , , - "" . - .

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

: , "" (-regexp) . .

Query

with
     species_str ( id, name) as (
       select 100, 'CfwHE3 (HH3d) Jt1 (CD-1)'         from dual union all
       select 101, '4GSdg-3t 22sfG/J (mdx (fq) KO)'   from dual union all
       select 102, 'Yf7mMjfel 7(tm1) (SCID)'          from dual union all
       select 103, 'B29fj;jfos x11 (tmos (line x11))' from dual union all
       select 104, 'B29;CD (Atm (line G5))'           from dual union all
       select 105, 'Ifkso30 jel-3'                    from dual union all
       select 106, '13GupSip (te3x) Blhas/J'          from dual union all
       select 151, ''                                 from dual union all
       select 152, 'try (this (and (this))) ok?'      from dual union all
       select 153, 'try (this (and (this)) ok?)'      from dual union all
       select 154, 'try (this (and) this (ok))?'      from dual union all
       select 155, 'try (this (and (this)'            from dual union all
       select 156, 'right grouping (includging ")")'  from dual union all
       select 157, 'try this out ) ( too'             from dual
     ),
     prep ( id, name, pos ) as (
       select id, name, instr(name, ')', -1)
       from   species_str
     ),
     rec ( id, name, str, len, prev_pos, new_pos, flag ) as (
       select  id, name, substr(name, 1, instr(name, ')', -1)),
               pos, pos - 1, pos, null
         from  prep
       union all
       select  id, name, str, len, new_pos,
               instr(str, '(',  -(len - new_pos + 2)),
               case when length(replace(substr(str, new_pos), '(', '')) =
                         length(replace(substr(str, new_pos), ')', ''))
                    then 1 end
         from  rec
         where prev_pos > 0 and flag is null
     )
select   id, name, case when flag = 1 
              then substr(name, prev_pos, len - prev_pos + 1) end as target
from     rec
where    flag = 1 or prev_pos <= 0 or name is null
order by id;


        ID NAME                             TARGET                         
---------- -------------------------------- --------------------------------
       100 CfwHE3 (HH3d) Jt1 (CD-1)         (CD-1)                          
       101 4GSdg-3t 22sfG/J (mdx (fq) KO)   (mdx (fq) KO)                   
       102 Yf7mMjfel 7(tm1) (SCID)          (SCID)                          
       103 B29fj;jfos x11 (tmos (line x11)) (tmos (line x11))               
       104 B29;CD (Atm (line G5))           (Atm (line G5))                 
       105 Ifkso30 jel-3                                                    
       106 13GupSip (te3x) Blhas/J          (te3x)                          
       151                                                                  
       152 try (this (and (this))) ok?      (this (and (this)))             
       153 try (this (and (this)) ok?)      (this (and (this)) ok?)         
       154 try (this (and) this (ok))?      (this (and) this (ok))          
       155 try (this (and (this)            (this)                          
       156 right grouping (includging ")")                                  
       157 try this out ) ( too                                             

 14 rows selected

, OP ():

select ( ), case when flag = 1 then... target, :

... , case when flag = 1 and len = length(name) then ...

:

        ID NAME                             TARGET                         
---------- -------------------------------- --------------------------------
       100 CfwHE3 (HH3d) Jt1 (CD-1)         (CD-1)                          
       101 4GSdg-3t 22sfG/J (mdx (fq) KO)   (mdx (fq) KO)                   
       102 Yf7mMjfel 7(tm1) (SCID)          (SCID)                          
       103 B29fj;jfos x11 (tmos (line x11)) (tmos (line x11))               
       104 B29;CD (Atm (line G5))           (Atm (line G5))                 
       105 Ifkso30 jel-3                                                    
       106 13GupSip (te3x) Blhas/J                                          
       151                                                                  
       152 try (this (and (this))) ok?                                      
       153 try (this (and (this)) ok?)      (this (and (this)) ok?)         
       154 try (this (and) this (ok))?                                      
       155 try (this (and (this)            (this)                          
       156 right grouping (includging ")")                                  
       157 try this out ) ( too                                             

 14 rows selected
+1

, Oracle , PCRE .NET. , .

regex 1 :

\([^()]*(\([^()]*\)[^()]*)*\)$

regex

Test (get (me) ((me), too)). , , 1 , , 2 , , .

:

  • \( - a (
  • [^()]* - , ( )
  • (\([^()]*\)[^()]*)* - :
    • \( a (
    • [^()]* - , ( )
    • \) - )
    • [^()]* - , ( )
  • \) - )
  • $ - .

regexp_substr(col_name, '\([^()]*(\([^()]*\)[^()]*)*\)$', 1, 1)
+1

, :

create or replace
function fn_pars(p_text in varchar2) return varchar2 deterministic as 
  n_count pls_integer := 0;
begin
  if p_text is null or instr(p_text, ')', -1) = 0
    or p_text not like '%)' then
    return null;
  end if;
  for i in reverse 1..length(p_text) loop
    case substr(p_text, i, 1) 
      when ')' then n_count := n_count + 1;
      when '(' then n_count := n_count - 1;
      else null;
    end case;
    if n_count = 0 then 
      return substr(p_text, i);
    end if;
  end loop;
  return p_text;
end fn_pars;

:

select text,
       fn_pars(text)
  from (
          select 'B29fj;jfos x11 (tmos (line x11)) abc' text from dual union all
          select 'B29fj;j(fos) x11 (tmos (line x11))' text from dual union all
          select 'B29fj;j(fos) x11 (t(mo)s (line x11))' text from dual union all
          select '' text from dual union all
          select 'no parentheses' text from dual
       )

:

Text                                    fn_pars(text)
-----------------------------------------------------
B29fj;jfos x11 (tmos (line x11)) abc   (null)
B29fj;j(fos) x11 (tmos (line x11))     (tmos (line x11))
B29fj;j(fos) x11 (t(mo)s (line x11))   (t(mo)s (line x11))
(null)                                 (null)
no parentheses                         (null)

(null) .:)

. .

+1

, :

regexp_substr(col, '[(]([^()]*|[(][^()]*[)])+[)]$', 1, 1)

, .

0

:

Oracle:

CREATE TABLE species_strain ( id, name ) AS
SELECT 100,   'CfwHE3 (HH3d) Jt1 (CD-1)' FROM DUAL UNION ALL
SELECT 101,   '4GSdg-3t 22sfG/J (mdx (fq) KO)' FROM DUAL UNION ALL
SELECT 102,   'Yf7mMjfel 7(tm1) (SCID)' FROM DUAL UNION ALL
SELECT 103,   'B29fj;jfos x11 (tmos (line x11))' FROM DUAL UNION ALL
SELECT 104,   'B29;CD (Atm (line G5))' FROM DUAL UNION ALL
SELECT 105,   'Ifkso30 jel-3' FROM DUAL UNION ALL
SELECT 106,   'data (1 (2 (333 (444) 3 (4 (5) 4) 3) 2) 1)' FROM DUAL;

1:

WITH tmp ( id, name, pos, depth ) AS (
  SELECT id,
         name,
         LENGTH( name ),
         1
  FROM   species_strain
  WHERE  SUBSTR( name, -1 ) = ')'
UNION ALL
  SELECT id,
         name,
         pos - 1,
         depth + CASE SUBSTR( name, pos - 1, 1 )
                 WHEN '(' THEN -1
                 WHEN ')' THEN +1
                          ELSE 0 END
  FROM   tmp
  WHERE  (  depth > 1
         OR SUBSTR( name, pos -1, 1 ) <> '(' )
  AND    pos > 0
)
SELECT id,
       MAX( name ) AS name,
       MIN( SUBSTR( name, pos - 1 ) ) KEEP ( DENSE_RANK FIRST ORDER BY pos )
         AS bracket
FROM   tmp
GROUP BY id;

2:

SELECT id,
       name,
       substr( name, start_pos ) AS bracket
FROM   (
  SELECT id,
         name,
         LAG( CASE WHEN bracket = '(' AND depth = 1 THEN pos END )
           IGNORE NULLS OVER ( PARTITION BY id ORDER BY ROWNUM )
           AS start_pos,
         pos AS end_pos,
         bracket,
         depth
  FROM   (
    SELECT id,
           name,
           COLUMN_VALUE AS pos,
           SUBSTR( name, column_value, 1 ) AS bracket,
           SUM( CASE SUBSTR( name, column_value, 1 ) WHEN '(' THEN 1 ELSE -1 END )
             OVER ( PARTITION BY id ORDER BY ROWNUM ) AS depth
    FROM   species_strain s,
           TABLE(
             CAST(
               MULTISET(
                 SELECT REGEXP_INSTR( s.name, '[()]', 1, LEVEL )
                 FROM   DUAL
                 CONNECT BY LEVEL <= REGEXP_COUNT( s.name, '[()]' )
               ) AS SYS.ODCINUMBERLIST
             )
           ) t
    WHERE  SUBSTR( s.name, -1 ) = ')'
  )
)
WHERE bracket = ')'
AND   end_pos = LENGTH( name );


        ID NAME                                       BRACKET                                  
---------- ------------------------------------------ ------------------------------------------
       100 CfwHE3 (HH3d) Jt1 (CD-1)                   (CD-1)                                     
       101 4GSdg-3t 22sfG/J (mdx (fq) KO)             (mdx (fq) KO)                              
       102 Yf7mMjfel 7(tm1) (SCID)                    (SCID)                                     
       103 B29fj;jfos x11 (tmos (line x11))           (tmos (line x11))                          
       104 B29;CD (Atm (line G5))                     (Atm (line G5))                            
       106 data (1 (2 (333 (444) 3 (4 (5) 4) 3) 2) 1) (1 (2 (333 (444) 3 (4 (5) 4) 3) 2) 1)
0

Source: https://habr.com/ru/post/1652912/

More articles:


All Articles