Oracle Substr and Instr example

Sample table is having column as follows.

Column1
category1subcat1
category1subcat1
category2subcat2
category3subcat1
category2subcat3
category3subcat1
category2subcat2
category1subcat1

SELECT unique(SUBSTR(Column1, 1 ,INSTR(Column1, '', 1, 1)-1)) as region
from abcd.table1 where Column1 like '%%'

This query gives result
category1
category2
category3

SELECT unique(SUBSTR(Column1, INSTR(Column1, '', -1, 1)+1)) as userid
from abcd.table1 where Column1 like '%%'

This query gives result
subcat1
subcat2
subcat3

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s