6 Replies Latest reply: Oct 27, 2011 6:52 AM by MichaelR64 RSS

    Another REGEXP question: how to extract multiple values from one string

    MichaelR64
      Hi, how do i extract multiple values from a string with RegExp constructs with Oracle SQL?

      I have looked at the various examples but can not figure this out .
      the following Sql
      select
         regexp_substr(sessie."rollen", 'CN=A_role') "A_role"
         from ( 
      select '
      CN=A_role,OU=a_org_unit,OU=another_org_unit,DC=bz,DC=ad,DC=min,DC=local
      CN=Another_role,OU=some_org_unit,DC=bz,DC=ad,DC=min,DC=local
      CN=Users,OU=Dep,DC=bz,DC=ad,DC=min,DC=local
      '   "rollen" from dual
      ) sessie;
      just returns CN=A_role.
      I want it to return all of the CN= values concatenated with a semicolon and stripped from the CN= bit.
      So: A_role;Another_role;Users .

      Any help would be deeply appreciated. I am using 11g 11.2.0.2.0 database here.

      Does anybody have any idea how to do this in SQL ?

      kind regards Mike
        • 1. Re: Another REGEXP question: how to extract multiple values from one string
          AndreyN
          Hi,
          If use Oracle 11g
          with tst
            as (select   'CN=A_role,OU=a_org_unit,OU=another_org_unit,DC=bz,DC=ad,DC=min,DC=local,'
                       ||'CN=Another_role,OU=some_org_unit,DC=bz,DC=ad,DC=min,DC=local,'
                       ||'CN=Users,OU=Dep,DC=bz,DC=ad,DC=min,DC=local' as rollen 
                  from dual
                )   
          select listagg(sub, ',') within group (order by rn) as res
            from (select regexp_substr(rollen, '(CN=)([[:alnum:]_]+)',1,rownum,'i',2) sub, rownum rn
                    from tst
                 connect by level <=length(rollen)  
                 )
           where sub is not null       
          • 2. Re: Another REGEXP question: how to extract multiple values from one string
            MichaelR64
            My gratitude is great ... thank you !

            Hmm strange , i am using it in OBIEE 10g to parse the LDAP memberOf attribute.
            That holds all groups a person is member of in the LDAP directory.
            That probably returns a multi-value field or string (?) because this code only returns the first value it can find.

            My sample code is based though on real-world data as can be seen with an LDAP explorer.
            I had it formatted with newlines for every "row" so perhaps your solution doesn't handle this properly.

            I am using this to generate the groups:
            with tst
              as (select  'VALUEOF(NQ_SESSION.MEMBEROF)' as rollen 
                    from dual
                  )   
            select listagg(sub, ';') within group (order by rn) as res
              from (select regexp_substr(rollen, '(CN=)([[:alnum:]_]+)',1,rownum,'i',2) sub, rownum rn
                      from tst
                   connect by level <=length(rollen)  
                   )
             where sub is not null 
            Any insights on how to solve this ?

            Edited by: MichaelR64 on 26-okt-2011 15:55
            • 3. Re: Another REGEXP question: how to extract multiple values from one string
              AndreyN
              What is
              'VALUEOF(NQ_SESSION.MEMBEROF)'
              ?
              • 4. Re: Another REGEXP question: how to extract multiple values from one string
                MichaelR64
                Well that is the part that OBIEE recognizes and processes.
                It is a reference to a session variable in OBIEE that gets populated with all the groups that a person is member of in the LDAP directory.

                That part works, since i have used it before in another try at this.
                I guess it just comes out as some kind of multi-value variable.

                The entire sql is used as an LDAP Initialisation block in OBIEE so thats why i am "allowed" to use this variable here although you can not use it in SQL Developer.
                • 5. Re: Another REGEXP question: how to extract multiple values from one string
                  AndreyN
                  >
                  Well that is the part that OBIEE recognizes and processes.
                  It is a reference to a session variable in OBIEE that gets populated with all the groups that a person is member of in the LDAP directory.

                  That part works, since i have used it before in another try at this.
                  I guess it just comes out as some kind of multi-value variable.

                  The entire sql is used as an LDAP Initialisation block in OBIEE so thats why i am "allowed" to use this variable here although you can not use it in SQL Developer.
                  >

                  What type of expression VALUEOF(NQ_SESSION.MEMBEROF)?
                  What result of
                  select VALUEOF(NQ_SESSION.MEMBEROF)
                    from dual;
                  ?
                  • 6. Re: Another REGEXP question: how to extract multiple values from one string
                    MichaelR64
                    The result is the example i used.
                    What type it is i do not know(yet).

                    I am looking into this, this is OBIEE stuff and the variable gets populated by the OBIEE server at runtime.