CODEWITHSUNDEEP
oracle-databaseoracle11g
GianIsTheName
GianIsTheName

Reputation: 187

String Manipulation in Oracle 11g

I am currently working on an exercise that will display a certain text, say TESTTEMP, from a series of characters stored in a column. Example of the string:

PROGRAMNAME|MAX2112ETI_L;PROGRAMREV|L;TESTOPTION|FR;TESTTEMP|25;STD IPH|528.63436123348

Now what I need is to extract the text past the string TESTTEMP| and before the ;. What I did is I extracted all the text past TESTTEMP| and just get the first two character. Unfortunately, this will not be possible since there are cases where in the TESTTEMP| has 3 characters. Is there a way for me to be able to do this? Below is what I have so far:

SELECT 
  SUBSTR(SUBSTR(value, INSTR(value, ';TESTTEMP|')+10), 1, 2) invalue
FROM
(
  SELECT 
    'PROGRAMNAME|MAX2112ETI_L;PROGRAMREV|L;TESTOPTION|FR;TESTTEMP|25;STD IPH|528.63436123348' VALUE 
  FROM dual
)t;

Upvotes: 0

Views: 212

Answers (1)

Praveen Lobo
Praveen Lobo

Reputation: 7187

Find the index of ; in the substring and use that as index instead of hard coding it to 2. 

SELECT 
  SUBSTR(SUBSTR(value, INSTR(value, ';TESTTEMP|')+10), 1, INSTR(SUBSTR(value, INSTR(value, ';TESTTEMP|')+10), ';')-1) invalue
FROM
(
  SELECT 
    'PROGRAMNAME|MAX2112ETI_L;PROGRAMREV|L;TESTOPTION|FR;TESTTEMP|25;STD IPH|528.63436123348' VALUE 
  FROM dual
)t;

If that look messy, you can even write it like this

SELECT
  SUBSTR(VALUE, 1, INSTR(VALUE, ';') - 1) invalue
FROM
(
  SELECT
     SUBSTR(VALUE, INSTR(VALUE, ';TESTTEMP|') + 10)
     VALUE
  FROM (
      SELECT
         'PROGRAMNAME|MAX2112ETI_L;PROGRAMREV|L;TESTOPTION|FR;TESTTEMP|25;STD IPH|528.63436123348'
         VALUE
      FROM dual) t
)t;

Based on comments - if you want to know if the string was found or not, use this query. FOUND will be greater than 0 if the string is found.

SELECT
  SUBSTR(VALUE, 1, INSTR(VALUE, ';')-1) invalue, FOUND
FROM
(
  SELECT
     SUBSTR(VALUE, INSTR(VALUE, SEARCHSTRING)+10) VALUE, INSTR(VALUE, SEARCHSTRING) FOUND
  FROM (
      SELECT
         'PROGRAMNAME|MAX2112ETI_L;PROGRAMREV|L;TESTOPTION|FR;TESTTEMP|25;STD IPH|528.63436123348'
         VALUE,
         ';TESTTEMP|' SEARCHSTRING
      FROM dual) t
)t;

Upvotes: 1

Related Questions