CODEWITHSUNDEEP
sqloracle-databaseplsql
Sreenath Reddy
Sreenath Reddy

Reputation: 490

Sql query to get value within the substring

I have a column with values as,

CompanyDetails

Id=18, company=3,org=[objectId=18, objectName=Library], parentOrg=null 
Id=10009,company=company=[companyId=3, companyName=CONE],org=[objectId=18, objectName=Library], parentOrg=[objectId=10001, objectName=Cyb 1]
Id=10008, company=3,org=[objectId=10005, objectName=Cyb 5], parentOrg=[objectId=10004, objectName=Cyb 4]
Id=10007, company=3,org=[objectId=10004, objectName=Cyb 4], parentOrg=[objectId=10003, objectName=Cyb 3],

The data type of the above column is 'VARCHAR2'.

Now I want to get value of all object Ids in the above column something like this.

OrgId
----
18
1004
1005

Upvotes: 2

Views: 298

Answers (1)

Noel
Noel

Reputation: 10525

If you are sure that object id is the 3rd value, you can use regex_substr to do this.

SELECT REGEXP_SUBSTR (column, '\d+', 1, 3)
  FROM table;

You can also use regexp_replace, in case it is not the 3rd value always.

SELECT REGEXP_REPLACE (column, '(org=\[objectId=)(\d+)|(.)', '\2')
  FROM table;

Upvotes: 3

Related Questions