跨多列拆分字符串的元素 [英] Splitting elements of a string across multiple columns

问题描述

我想在 MySQL 中将包含带有文章引用的字符串的列拆分为多个列.例如下面的字符串

I would like to split a column containing a string with article references into multiple columns in MySQL. For example, the following string

North American Birds 53(1) 1999: 27-29

将被拆分为 'North American Birds', 53, 1, 1999, 27-19.我知道我可以使用 substring_index 来做一些这样的事情，但这不适用于从卷 (53) 中拆分期刊名称 (North American Birds).知道我该怎么做吗?

would be split into 'North American Birds', 53, 1, 1999, 27-19. I know I can do some of this with substring_index, but this will not work for splitting the journal name (North American Birds) from the volume (53). Any idea how I can do that?

推荐答案

您可以使用数字作为分隔符.

You can use the number as a separator.

缓慢而丑陋的代码，但我想它可以工作

SELECT 
  s.id
  ,SUBSTRING(s.title,1, PosOfFirstNumber-1) as booktitle
  ,SUBSTRING(s.title, PosOfFirstNumber) as Remainder
FROM 
  (SELECT
      id
      ,title
      ,LEAST(
          IFNULL(NULLIF(LOCATE('1',title),0),999)
          ,IFNULL(NULLIF(LOCATE('2',title),0),999)
          ,IFNULL(NULLIF(LOCATE('3',title),0),999)
          ,IFNULL(NULLIF(LOCATE('4',title),0),999)
          ,IFNULL(NULLIF(LOCATE('5',title),0),999)
          ,IFNULL(NULLIF(LOCATE('6',title),0),999)
          ,IFNULL(NULLIF(LOCATE('7',title),0),999)
          ,IFNULL(NULLIF(LOCATE('8',title),0),999)
          ,IFNULL(NULLIF(LOCATE('9',title),0),999)
          ,IFNULL(NULLIF(LOCATE('0',title),0),999)
        )) as PosOfFirstNumber
    FROM table1 ) s

这篇关于跨多列拆分字符串的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！