mysql - MySql 中分割逗号 (,) 和脱字号 (^) 分隔值

Question

我正在尝试将列字符串值拆分为单独的行，但到目前为止还没有成功。

这是我的数据集

990^275,987^280,988^278

我想获取每个逗号分隔数据集的第一个值 i.e. 990,987,988 etc 。我已经尝试过substring_index但没有得到我想要的结果。这是我尝试过的 -

select  
  rooms_tariffs, 
  replace( rooms_tariffs,'^',','), 
  SUBSTRING_INDEX(replace( rooms_tariffs,'^',','), ',', 1) "First",
  SUBSTRING_INDEX(SUBSTRING_INDEX(rooms_tariffs,'^',2),',', -1) "second"
from ewqhv_jomres_contracts 
order by 1 desc;

输出 -

rooms_tariffs | rooms_tariffs | First | second
991^278,990^275 991,278,990,275 991 990
991^278,984^280 991,278,984,280 991 984
991^278 991,278 991 991^278
991^278 991,278 991 991^278

Answer 1

你就在附近，我的 friend 。试试这个:

SELECT    `data`.`room_tariffs` AS `room_tariffs`,
          SUBSTRING_INDEX(`data`.`room_tariffs`, ',', 1) AS `First`,
          SUBSTRING_INDEX(SUBSTRING_INDEX(`data`.`room_tariffs`, ',', -4), ',', 1) AS `Second`,
          SUBSTRING_INDEX(SUBSTRING_INDEX(`data`.`room_tariffs`, ',', -2), ',', 1) AS `Third`
FROM (
     SELECT    REPLACE(`room_tariffs`, '^', ',') AS `room_tariffs` 
     FROM      `ewqhv_jomres_contracts` 
     ORDER BY  1 desc;
     ) AS `data`

输出:

room_tariffs                First   Second  Third   
990,275,987,280,988,278     990     987     988