Question on String Function

[ChandruN]

I have a string "ABC_102_EDOT_R001"

I want to extract R001.

I have tried INDEX & POSITION. That did not help.

I would appreciate your help ..

Thanks !!

 

[BillDHS]

SUBSTRING (col1 FROM 14 FOR 4)

 

[jgerstb]

If you need to find the string following the last the _ you could try to search from the end of a known length string like this (not pretty)
SELECT
CASE
WHEN SUBSTRING (my_string FROM 16 FOR 1) = '_'
THEN TRIM(SUBSTRING (my_string FROM 17))
WHEN SUBSTRING (my_string FROM 15 FOR 1) = '_'
THEN TRIM(SUBSTRING (my_string FROM 16))
WHEN SUBSTRING (my_string FROM 14 FOR 1) = '_'
THEN TRIM(SUBSTRING (my_string FROM 15))
WHEN SUBSTRING (my_string FROM 13 FOR 1) = '_'
THEN TRIM(SUBSTRING (my_string FROM 14))
etc
etc

END

 

[ChandruN]

ABC_102_EDOT_R001
EXS_456_11
FFF_RRRR_DDD_444_ASG_E903
RT1_W101

My aim is to find the last "_". The above values are stored in a single column. I want to write a sql to get

R001
11
E903
W101

 

[BillDHS]

3 alternatives, no easy way to do it.

1. Locate each _ and partition each section into a different column. Hope you don't have too many sections.

2. Although I am not a BASIC/PERL/COBOL programmer, I believe you can move the table to a file, partition it programatically and reload the tabble.

3. If you have V2R5 (I don't) you amy be able to create or find a user defined function (UDF) to perform the task.