How to extract substring from a column based on spl char like '/'

Database

How to extract substring from a column based on spl char like '/'

Hi All,

I am having a Full name column which consists of values like "ABC / XYZ"

I want to extract the sub string before '/' into a column, sub string after '/' into another column.

Please suggest me on this.

Thanks,

Subash

7 REPLIES

Re: How to extract substring from a column based on spl char like '/'

ct tt (v varchar(20));

sel substr(v,0,position('/' in v)) from tt;

Re: How to extract substring from a column based on spl char like '/'

sel substr(v,0,position('/' in v)),substr(v,position('/' in v)+1,length(v))

 from tt;

 *** Query completed. One row found. 2 columns returned.

 *** Total elapsed time was 1 second.

Substr(v,0,Position('/' in v))  Substr(v,(Position('/' in v)+1),length(v))

------------------------------  ------------------------------------------

abc                             xyz

Enthusiast

Re: How to extract substring from a column based on spl char like '/'

sel Trim(substr('ABC / XYZ',1,position('/' in 'ABC / XYZ') - 1)) COL1,
Trim(substr('ABC / XYZ',position('/' in 'ABC / XYZ') + 1)) COL2

Rglass

Senior Apprentice

Re: How to extract substring from a column based on spl char like '/'

strtok(col, '/', 1),  strtok(col, '/', 2)

Re: How to extract substring from a column based on spl char like '/'

Hi Dieter,

thanks its working...

Re: How to extract substring from a column based on spl char like '/'

Hi

all,I have the following string '12_020_00003290960_abcd'. What I want to do is take the characters from 2nd special chanracter to 3rd special character and remove remening data . Is there a way to do this? I have tried substring but can't seem to figure it out.Thanks in advance

Substring Value : '12_020_00003290960_abcd'

Expected Out put :  00003290960

 
Senior Apprentice

Re: How to extract substring from a column based on spl char like '/'

strtok(col, '_', 3)