Oracle 如何从SQL中的URL字符串中提取数据？

嗨，汤姆，我正在尝试从URL中提取一些数据，并遇到一些问题。URL的将看起来像这样:

http://192.168.201.242:8000/express_get?time=$startDate&from=$phoneNumber&to=$CAMPAIGN&Id_agent=$idExternalUser&spent=$duration&qualifId=$test&compagneId=$CAMPAIGN


我正在尝试返回列名和与之相对应的数据。
(例如): 时间 = $ startDate
从 = $ 电话号码

关于如何正确执行此操作的任何建议？

看看我的一个朋友莫滕·伊根写的URL_NINJA。

https://github.com/morten-egan/url_ninja

但是如果你想要SQL，这里有一些示例

1) 得到配对

SQL> with t as
  2  ( select 'http://192.168.201.242:8000/express_get?time=XXXXX&from=YYYYY&to=JJJJJ&Id_agent=LLLLLL&spent=1234&qualifId=HHHHH&compagneId=UUUUUU' x from dual )
  3  ,
  4  params as
  5  ( select '&'||substr(x,instr(x,'?')+1) p from t )
  6  select
  7     regexp_substr (p, '[^&]+', 1, level)
  8  from params
  9  connect by level <= regexp_count(p,'&');

REGEXP_SUBSTR(P,'[^&]+',1,LEVEL)
----------------------------------------------------------------------------------------------------------------------------------
time=XXXXX
from=YYYYY
to=JJJJJ
Id_agent=LLLLLL
spent=1234
qualifId=HHHHH
compagneId=UUUUUU

2) 获取键/值

SQL> with t as
  2  ( select 'http://192.168.201.242:8000/express_get?time=XXXXX&from=YYYYY&to=JJJJJ&Id_agent=LLLLLL&spent=1234&qualifId=HHHHH&compagneId=UUUUUU' x from dual )
  3  ,
  4  params as
  5  ( select '&'||substr(x,instr(x,'?')+1) p from t ),
  6  pairs as (
  7  select
  8     regexp_substr (p, '[^&]+', 1, level) pair
  9  from params
 10  connect by level <= regexp_count(p,'&')
 11  )
 12  select
 13    substr(pair,1,instr(pair,'=')-1) p1,
 14    substr(pair,instr(pair,'=')+1) p2
 15  from pairs;

P1                   P2
-------------------- --------------------
time                 XXXXX
from                 YYYYY
to                   JJJJJ
Id_agent             LLLLLL
spent                1234
qualifId             HHHHH
compagneId           UUUUUU