在第八值和数字值之间获取字符:Oracle

Question

我有一个以下字符串

"923449499950,49,3513,NewBasic5,Talkshawk,IDKR17,North-2,KARAK,"Village Warana Mir Hassan Khel ,Dist Karak Tehsil,459,N2-NKRK1201,.0000,.0000,.00,.00,.00,.00,0,.0000,.0000,.0000,.00,.00,.0000,.00,.00,Nokia,108,Basic-Voice Only,Proprietary OS,Nokia 108"

我想在karak之间提取字符串和459。

我无法破解这个

SELECT nth_string
FROM
  (SELECT TRIM (REGEXP_SUBSTR (long_string, '[^,]+', 1, ROWNUM) ) nth_string ,
    level AS lvl
  FROM
    (SELECT REPLACE('923449499950,49,3513,NewBasic5,Talkshawk,IDKR17,North-2,KARAK,"Village Warana Mir Hassan Khel ,Dist Karak Tehsil,459,N2-NKRK1201,.0000,.0000,.00,.00,.00,.00,0,.0000,.0000,.0000,.00,.00,.0000,.00,.00,Nokia,108,Basic-Voice Only,Proprietary OS,Nokia 108',
    ',,',', ,') long_string
    FROM DUAL
    )
    CONNECT BY LEVEL <= REGEXP_COUNT ( long_string, '[^,]+')
  )WHERE lvl = 9;

因此，由此产生的字符串将是" Warana Mir Hassan Khel，Dist Karak Tehsil"。我使用的代码给出了" Warana Mir Hassan Khel"。

Answer 1

以下是一个选项：

SQL> with test (col) as
  2    (select '"923449499950,49,3513,NewBasic5,Talkshawk,IDKR17,North-2,KARAK,"Village Warana Mir Hassan Khel ,Dist Kar
ak Tehsil,459,N2-NKRK1201,.0000,.0000,.00,.00,.00,.00,0,.0000,.0000,.0000,.00,.00,.0000,.00,.00,Nokia,108,Basic-Voice On
ly,Proprietary OS,Nokia 108"'
  3    from dual)
  4  select instr(col, ',', 1, 8) + 1 eig,            --> 8th comma position
  5         regexp_instr(col, 'd+', 1, 7) dig,       --> digit (459 in your example)
  6  --
  7  rtrim(ltrim(substr(col,
  8                     instr(col, ',', 1, 8) + 1 ,        --> starting position
  9                     regexp_instr(col, 'd+', 1, 7) - 1 - instr(col, ',', 1, 8) - 1 ),          --> length
 10        '"'), '"') result          --> remove double quotes (if any)
 11  from test;
       EIG        DIG RESULT
---------- ---------- -------------------------------------------------
        64        115 Village Warana Mir Hassan Khel ,Dist Karak Tehsil

您需要的是第7-10行。