2

I'm querying a dataset using Oracle SQL Developer and want to create a column with partial return data from a (CLOB) in another column.

The part I need is in quotes and i've used substring to extract however the value will change as will the length of the string. Is there a way to end when reach closing quotes instead of specifying length of string?

dbms_lob.substr(a.LINETESTRESULT,15,dbms_lob.instr(UPPER(a.LINETESTRESULT),'LHCRAG')+11) AS REFRESH_RAG

At the minute, 15 characters are returned, but the latest additions are shorter and showing the ", from the next entry. I need to replace the length attribute.

The result I get is:

Red_Session",

I need the output to be: Red_Session

or whatever the return value is, I just need it to end before the closing quote.

djd
  • 87
  • 1
  • 9

3 Answers3

2

INSTR has a parameter nth (Occurrence number, starting at 1.). With this you can create something like this. 

dbms_lob.substr(a.LINETESTRESULT,dbms_lob.instr(a.LINETESTRESULT, '"', 1, 2) - dbms_lob.instr(a.LINETESTRESULT, '"') - 1, dbms_lob.instr(a.LINETESTRESULT, '"') + 1) AS REFRESH_RAG
RGruca
  • 204
  • 1
  • 5
  • i've added the code, amended slightly to choose the start point, and it seems to return the right result, all bar one example - just going to test some more dbms_lob.substr(a.LINETESTRESULT,dbms_lob.instr(a.LINETESTRESULT, '"', 1, 2) - dbms_lob.instr(a.LINETESTRESULT, '"') + 1, dbms_lob.instr(a.LINETESTRESULT, 'LHCRAG') + 11) AS REFRESH_RAG – djd May 09 '19 at 12:48
1

you might try REGEXP_SUBSTR like below. Alternatively you can find the position of the quote and use the substr...

SELECT
  REGEXP_SUBSTR(a.LINETESTRESULT,
                '"([^"]*)') AS REFRESH_RAG
  FROM DUAL;
Dr Phil
  • 833
  • 6
  • 18
0

Managed to get this using the following code:

SELECT
dbms_lob.substr(UPPER(a.LINETESTRESULT), dbms_lob.instr(UPPER(a.LINETESTRESULT), '"', 
dbms_lob.instr(UPPER(a.LINETESTRESULT), 'LHCRAG') + 11) - 
(dbms_lob.instr(UPPER(a.LINETESTRESULT), 'LHCRAG') + 11), 
dbms_lob.instr(UPPER(a.LINETESTRESULT), 'LHCRAG') + 11)
AS REFRESH_RAG
djd
  • 87
  • 1
  • 9