Extract substring from string

59 Views

Hi,

Iu2019m reaching out to the experts in the forum for assistance with resolving a code issue in Universe Designer u2013 itu2019s not producing the expected result. The following are details, and steps taken:

Objective: To extract only the characters between #u2019s, i.e. u2013 Smith, John added comments on 12/1/11 V5M#0.25# this is a test. *NOTE* The characters before AND after the #u2019s varies.

Database u2013 Oracle 10g

Field type u2013 CLOB (DBAu2019s will not change) u2013 I created an object u201CDescriptionu201D= DBMS_LOB.SUBSTR("Oracle_Test_Unv". DESC, 4000,1) u2013 I also tried casting as a varchar2 and still the same result.

Object u2013 Code to extract the characters between #u2019s:

CASE WHEN @Select(Oracle_Test_Unv\Description) LIKE '%V5M#%#%'

THEN DBMS_LOB.SUBSTR(@Select(Oracle_Test_Unv\Description),

DBMS_LOB.INSTR(@Select(Oracle_Test_Unv\Description), '#',1,1)+1,

DBMS_LOB.INSTR(@Select(Oracle_Test_Unv\Description), '#',1,2) -

DBMS_LOB.INSTR(@Select(Oracle_Test_Unv\Description), '#',1,1)-1)

ELSE '0'

END

Current Result: Smith, John added comments on 12/1/11 V5M#0.25

Thanks in advance for your help!