CODEWITHSUNDEEP
sqloracle-database
user2961454
user2961454

Reputation: 353

Get substring with in special characters in oracle

I have table with below values in column.

cn=Admin,CN=Alerts,OU=App Entitlements,OU=Groups,DC=example,DC=com
cn=User,CN=Voice,OU=App Entitlements,OU=Groups,DC=example,DC=com
cn=None,CN=Call,OU=App Entitlements,OU=Groups,DC=example,DC=com

I am trying to write query which can fetch value between second = character and second , character. Like,

Alerts
Voice
Call

I wrote a query which is almost working but it is not trailing , at the end.

select substr(regexp_substr('cn=Admin,CN=Alerts,OU=App Entitlements,OU=Groups,DC=example,DC=com','[^,]+,',1,2),4) from dual

output from above query is showing as

Alerts,

But i don't need , at the end. I tried with substr method but it is messing up the query. Looks like i am missing simple thing. Can anyone suggest the correct output?

Upvotes: 0

Views: 791

Answers (2)

MT0
MT0

Reputation: 167867

Query 1 - Just get the second property value:

WITH Certificates( id, CertificateString ) AS (
  SELECT 1, 'cn=Admin,CN=Alerts,OU=App Entitlements,OU=Groups,DC=example,DC=com' FROM DUAL UNION ALL
  SELECT 2, 'cn=User,CN=Voice,OU=App Entitlements,OU=Groups,DC=example,DC=com' FROM DUAL UNION ALL
  SELECT 3, 'cn=None,CN=Call,OU=App Entitlements,OU=Groups,DC=example,DC=com' FROM DUAL
)
SELECT ID,
       REGEXP_SUBSTR( CertificateString,'([^,]+)=([^,]+)',1,2,NULL,2)  AS VALUE
FROM   Certificates c

Results:

        ID VALUE
---------- -------
         1 Alerts
         2 Voice
         3 Call

Query 2 - Get all the properties and values:

WITH Certificates( id, CertificateString ) AS (
  SELECT 1, 'cn=Admin,CN=Alerts,OU=App Entitlements,OU=Groups,DC=example,DC=com' FROM DUAL UNION ALL
  SELECT 2, 'cn=User,CN=Voice,OU=App Entitlements,OU=Groups,DC=example,DC=com' FROM DUAL UNION ALL
  SELECT 3, 'cn=None,CN=Call,OU=App Entitlements,OU=Groups,DC=example,DC=com' FROM DUAL
)
SELECT ID,
       REGEXP_SUBSTR( CertificateString,'([^,]+)=([^,]+)',1,COLUMN_VALUE,NULL,1) AS PROPERTY,
       REGEXP_SUBSTR( CertificateString,'([^,]+)=([^,]+)',1,COLUMN_VALUE,NULL,2) AS VALUE
FROM   Certificates c,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( c.CertificateString,'([^,]+?)=([^,]+)(,|$)' )
           )
           AS SYS.ODCINUMBERLIST
         )
       );

Results:

        ID PROPERTY VALUE              
---------- -------- --------------------
         1 cn       Admin                
         1 CN       Alerts               
         1 OU       App Entitlements     
         1 OU       Groups               
         1 DC       example              
         1 DC       com                  
         2 cn       User                 
         2 CN       Voice                
         2 OU       App Entitlements     
         2 OU       Groups               
         2 DC       example              
         2 DC       com                  
         3 cn       None                 
         3 CN       Call                 
         3 OU       App Entitlements     
         3 OU       Groups               
         3 DC       example              
         3 DC       com

Upvotes: 2

Alexo Po.
Alexo Po.

Reputation: 127

Why not trying

select substr(regexp_substr('cn=Admin,CN=Alerts,OU=AppEntitlements,OU=Groups,DC=example,DC=com','[^,]+',1,2),4) from dual

Difference is that you have '[^,]+,' and I have '[^,]+'.

Regexps are greedy by default, so it should work.

Upvotes: 2

Related Questions