sql从字符串中取出指定的值

[siwluxuefeng]

如何从字符串中取出指定的值？如字符串中以 & 分割字段，需要取出 miss=3,5的值，返回 3,5 .用什么函数？
oher字段里，
status=200&body_bytes_sent=45&remote_addr=10.235.43.53&miss=3,5&hit=0,3

[流浪川]

SELECT REPLACE(OUTPUT, 'miss=', '')
  FROM (SELECT REGEXP_SUBSTR('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3',
                             '[^&&]+',
                             1,
                             LEVEL) AS OUTPUT
          FROM DUAL
        CONNECT BY LEVEL <= LENGTH('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3') -
                   LENGTH(REPLACE('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3',
                                           '&')) - 4)
 WHERE OUTPUT LIKE 'miss=%'

[siwluxuefeng]

你好，我的map表里有个other字段，字符串长度不定，格式为如下： status=200&body_bytes_sent=45&remote_addr=10.235.43.53&miss=3,5&hit=0,3 我需要从map表的oter字段里，解析出miss=3,5的值，返回3,5,插入新表里。这个查询sql怎么写，逻辑是什么，谢谢

[sxq129601]

可以用instr+substr来截取&miss=

[siwluxuefeng]

引用 1 楼 hidanger521 的回复:

SELECT REPLACE(OUTPUT, 'miss=', '')
  FROM (SELECT REGEXP_SUBSTR('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3',
                             '[^&&]+',
                             1,
                             LEVEL) AS OUTPUT
          FROM DUAL
        CONNECT BY LEVEL <= LENGTH('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3') -
                   LENGTH(REPLACE('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3',
                                           '&')) - 4)
 WHERE OUTPUT LIKE 'miss=%'

你好，我的map表里有个other字段，字符串长度不定，格式为如下： status=200&body_bytes_sent=45&remote_addr=10.235.43.53&miss=3,5&hit=0,3 我需要从map表的oter字段里，解析出miss=3,5的值，返回3,5,插入新表里。这个查询sql怎么写，逻辑是什么，谢谢

[流浪川]

引用 4 楼 Linzhongyilisha 的回复:

[quote=引用 1 楼 hidanger521 的回复:]

SELECT REPLACE(OUTPUT, 'miss=', '')
  FROM (SELECT REGEXP_SUBSTR('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3',
                             '[^&&]+',
                             1,
                             LEVEL) AS OUTPUT
          FROM DUAL
        CONNECT BY LEVEL <= LENGTH('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3') -
                   LENGTH(REPLACE('status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3',
                                           '&')) - 4)
 WHERE OUTPUT LIKE 'miss=%'

你好，我的map表里有个other字段，字符串长度不定，格式为如下： status=200&body_bytes_sent=45&remote_addr=10.235.43.53&miss=3,5&hit=0,3 我需要从map表的oter字段里，解析出miss=3,5的值，返回3,5,插入新表里。这个查询sql怎么写，逻辑是什么，谢谢[/quote] [code=sql][/SELECT REPLACE(OUTPUT, 'miss=', '') FROM (SELECT REGEXP_SUBSTR(OTHER, '[^&]+', 1, LEVEL) AS OUTPUT FROM MAP CONNECT BY LEVEL <= LENGTH(OTHER) - LENGTH(REPLACE(OTHER, '&')) + 1) WHERE OUTPUT LIKE 'miss=%'code]

[流浪川]

SELECT REPLACE(OUTPUT, 'miss=', '')
  FROM (SELECT REGEXP_SUBSTR(OTHER, '[^&]+', 1, LEVEL) AS OUTPUT
          FROM MAP
        CONNECT BY LEVEL <= LENGTH(OTHER) - LENGTH(REPLACE(OTHER, '&')) + 1)
 WHERE OUTPUT LIKE 'miss=%'

[xiaoguanzhao]

仅供参考 with t as (select 'status=200&&body_bytes_sent=45&&remote_addr=10.235.43.53&&miss=3,5&&hit=0,3' other from dual ) select substr(t.other,instr(t.other,'miss=')+5,instr(t.other,'hit')-instr(t.other,'miss=')-6), instr(t.other,'miss=')+5,instr(t.other,'hit')-instr(t.other,'miss=')-6,t.other from t;