Sql query to get value within the substring

Question

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

To clarify, is org a column in the row of a table called CompanyDetails, or does each line represent a single value for the field CompanyDetails? — Jeffrey Blake
– Jeffrey Blake, Commented Aug 7, 2013 at 16:31

Noel · Accepted Answer · 2013-08-07 16:37:08Z

3

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;

answered Aug 7, 2013 at 16:37

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sreenath Reddy Over a year ago

Thank you verry much, it helped a lot to me

Collectives™ on Stack Overflow

Sql query to get value within the substring

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related