CODEWITHSUNDEEP
sqlsubstringoracle-sqldeveloperspecial-characters
djd
djd

Reputation: 87

How to return string within quotes

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.

Upvotes: 2

Views: 309

Answers (3)

djd
djd

Reputation: 87

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

Upvotes: 0

RGruca
RGruca

Reputation: 204

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

Upvotes: 2

Dr Phil
Dr Phil

Reputation: 880

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;

Upvotes: 1

Related Questions