CODEWITHSUNDEEP
sqloracle-database
AutoTester999
AutoTester999

Reputation: 616

How to extract string between two known strings in SQL Result and display it in another Column?

I am using SQL Developer.

Table Name: PRCQA.PRCBUILD_FJ_BAT

Column Name: ETA

Value of 'ETA':

http://reefoats.us.oc.com:8080/atsresultviewer/displayresult?ree_run_id=153464685&type=ree

I am trying to extract the string 153464685 from the above resulting column and display it separately.

I have tried this till now:

SELECT SUBSTR(t.ETA, INSTR(t.ETA, '=')+1, INSTR(t.ETA, 'type')-1), t.ETA AS output
  FROM PRCQA.PRCBUILD_FJ_BAT t
  WHERE t.DTE_ID = '33782451' AND t.BAT_NAME LIKE '110_170_ECM 140_%'

I am getting the below value as result:

153464685&type=ree

But I want the result as: 153464685

Even if I change the INSTR(t.ETA, 'type')-1, still getting the same result?

What can I do here? Please suggest. Thanks.

Upvotes: 0

Views: 3025

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use regexp_substr():

select translate(regexp_substr(eta, '[=][^=&]+[&]', 1, 1), '=&a', 'a')
from PRCQA.PRCBUILD_FJ_BAT t
where t.DTE_ID = '33782451' and t.BAT_NAME like '110_170_ECM 140_%'

Upvotes: 1

AutoTester999
AutoTester999

Reputation: 616

This worked:

SELECT SUBSTR(t.ETA, INSTR(t.ETA, '=')+1, (INSTR(t.ETA, 'type')-INSTR(t.ETA, '='))-2), t.ETA AS output
  FROM PRCQA.PRCBUILD_FJ_BAT t
  WHERE t.DTE_ID = '33782451' AND t.BAT_NAME LIKE '110_170_ECM 140_%'

Upvotes: 0

Related Questions