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

Question

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.

Gordon Linoff · Accepted Answer · 2016-07-28 01:01:47Z

1

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_%'

answered Jul 28, 2016 at 1:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AutoTester999 · Accepted Answer · 2016-07-28 01:01:25Z

0

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_%'

answered Jul 28, 2016 at 1:01

AutoTester999

6882 gold badges11 silver badges31 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related