mysql - SQL:在分隔符与分隔符本身之间进行选择，插入其他列并删除字符串

Question

我没有为每组值设置一列，而是创建了一个名为“data”的列，并使用如下 HTML:

<dt>Phone:</dt><dd>0 23 16/3 82 73 42 23</dd>
<dt>Phone:</dt><dd>0 21 61/81 26 73 13 22</dd>
<dt>Fax:</dt><dd>03 27/3 87 42 37 32</dd>
<dt>Website:</dt><dd>www.example.com</dd>

现在，我意识到这不是很聪明，我为每个值创建了一个专栏。我的新列名称是“phone”、“phone2”、“fax”和“website”。

我需要一个 SQL 代码，例如选择分隔符 <dt>Phone:</dt><dd> 之间的所有内容和</dd>和分隔符本身，将此字符串插入“phone”列中，并删除“data”列中的此字符串。但我需要选择第一个字符串 <dt>Phone:</dt><dd>0 23 16/3 82 73 42 23</dd>不是第二个<dt>Phone:</dt><dd>0 21 61/81 26 73 13 22</dd> .

有人可以给我提示如何做到这一点吗？

Answer 1

用于选择 <dt>Phone:</dt><dd> 之间的数据和</dd>您可以使用 SUBSTRING_INDEX。

像这样

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(data, '</dd>', 1), '<dt>Phone:</dt><dd>', -1) as phone,
  SUBSTRING_INDEX(SUBSTRING_INDEX(data, '<dt>Phone:</dt><dd>', -1), '</dd>', 1) as phone2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(data, '<dt>Fax:</dt><dd>', -1), '</dd>', 1) as fax,
  SUBSTRING_INDEX(SUBSTRING_INDEX(data, '<dt>Website:</dt><dd>', -1), '</dd>', 1) as website
from data_col;

更新:

<dt>Phone:</dt> is not always in top.

如果“数据”列中没有指定的数据顺序，请尝试以下操作:

SELECT
    IF (temp.f_phone > 0, SUBSTR(data, temp.f_phone + LENGTH('<dt>Phone:</dt><dd>'), f_phone_end - temp.f_phone - LENGTH('<dt>Phone:</dt><dd>')), null) as PHONE_1,
    IF (temp.s_phone > 0, SUBSTR(data, temp.s_phone + LENGTH('<dt>Phone:</dt><dd>'), s_phone_end - temp.s_phone - LENGTH('<dt>Phone:</dt><dd>')), null) as PHONE_2
  from data_col dc
JOIN (
  SELECT id, @f_phone:= LOCATE('<dt>Phone:</dt><dd>', data) as f_phone,
  LOCATE('</dd>', data, @f_phone+1) f_phone_end,
  @s_phone := LOCATE('<dt>Phone:</dt><dd>', data, @f_phone+1) as s_phone,
  LOCATE('</dd>', data, @s_phone+1) as s_phone_end
from data_col) temp ON temp.id = dc.id;

首先找到每个可能元素的起始位置(例如“phone”“phone2”)和结束标签的位置 <\dd> 。比使用SUBSTR从元素的起始位置 + 分隔符的长度开始，其中 length = end_position - start_position - delimiter_length