Splitting elements of a string across multiple columns

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

would be split into 'North American Birds', 53, 1, 1999, 27-19. I know I can do some of 开发者_StackOverflow社区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.

Slow and ugly code, but I guess it works

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

First question to ask is, do you plan to split your string into different columns before OR after inserting it into your database? I suggest splitting before insertion, but this suggestion may be too late.

If splitting BEFORE insertion, you should use regular expressions to parse your data and then build the appropriate SQL INSERT. Here's a Java regular expressions example.

If splitting data after it's already in your database, you will need to create a SQL with pattern matching specific to the string your data. Here's a IBM article describing different SQL code strategies.