Use regexp to separate characters from digits in a column

Question

I'm trying to figure out how to group concat regexp. See an example of what I want to do (but keep in mind that I have thousand or rows so I can't do it one by one):

first_name  
  Maria05aa      
  John89bcb       
  George07
  Angie53cs

My results would look like this:

colummn_a       column_b     column_c
Maria.05.aa     Maria.aa       05
John8.a9.bcb    John.bcb       a9
George.07       George         07
Angie.b53.cs    Angiecs        b53

How can I achieve these results?

Can you guarantee that all names are like that? What if one came in with `54arm05bla` ? — AdamMc331, Jul 08 '15 at 16:04
If it's letters-number-letters, that's easy. In reality names can be different, can they not? Your results, to my eyes, also lack consistency. Why is there `b` added in 4th row? `a` in middle of 89 in the second row? — Andris Leduskrasts, Jul 08 '15 at 16:05
Do it in your client language; it is better suited for such than SQL. — Rick James, Jul 08 '15 at 18:47

score 0 · Answer 1 · edited May 23 '17 at 12:17

I first would reference the answer to this question which is to find the position of the first number in a string. You can adjust that same query to find the position of the last number in the string. So, to start, I calculated those values:

SELECT first_name,
  LEAST(
    if (LOCATE('0', first_name) > 0, LOCATE('0', first_name), 101),
    if (LOCATE('1', first_name) > 0, LOCATE('1', first_name), 101),
    if (LOCATE('2', first_name) > 0, LOCATE('2', first_name), 101),
    if (LOCATE('3', first_name) > 0, LOCATE('3', first_name), 101),
    if (LOCATE('4', first_name) > 0, LOCATE('4', first_name), 101),
    if (LOCATE('5', first_name) > 0, LOCATE('5', first_name), 101),
    if (LOCATE('6', first_name) > 0, LOCATE('6', first_name), 101),
    if (LOCATE('7', first_name) > 0, LOCATE('7', first_name), 101),
    if (LOCATE('8', first_name) > 0, LOCATE('8', first_name), 101),
    if (LOCATE('9', first_name) > 0, LOCATE('9', first_name), 101)
  ) AS firstNumberIndex,
  GREATEST(
    LOCATE('0', first_name),
    LOCATE('1', first_name),
    LOCATE('2', first_name),
    LOCATE('3', first_name),
    LOCATE('4', first_name),
    LOCATE('5', first_name),
    LOCATE('6', first_name),
    LOCATE('7', first_name),
    LOCATE('8', first_name),
    LOCATE('9', first_name)
  ) AS lastNumberIndex
FROM myTable;

I arbitrarily chose 101 as the max value, because I set the length of my string column to 100, so I chose an index that couldn't exist.

Once I had those, I used that as a subquery to get the substrings to the left of the first number, and to the right of the last number, as well as between them, to get the columns you wanted like this:

SELECT SUBSTRING(first_name, 1, firstNumberIndex - 1) AS firstPiece,
   SUBSTRING(first_name, firstNumberIndex, (lastNumberIndex - firstNumberIndex + 1)) AS numbers, 
   SUBSTRING(first_name, lastNumberIndex + 1) AS lastPiece
FROM(
    mySubquery) tmp;

Then, the only thing left to do was put them together in the format that you want. Again, I used a subquery to make this more readable, but the subquery isn't necessary as all data is coming from the same table. However, for something is complicated, I felt readability was an important compromise. I noticed you don't want a period added before empty substrings, so I had to write a few CASE statements:

SELECT
  CASE 
    WHEN numbers = '' THEN firstPirce
    ELSE
      CASE 
        WHEN lastPiece = '' THEN CONCAT(firstPiece, '.', numbers)
        ELSE CONCAT(firstPiece, '.', numbers, '.', lastPiece)
      END
  END AS column_a,
  CASE 
    WHEN lastPiece = '' THEN firstPiece
    ELSE CONCAT(firstPiece, '.', lastPiece)
  END AS column_b,
  numbers AS column_c
FROM(
   myHugeSubquery) tmp;

Here is an SQL Fiddle example.

Use regexp to separate characters from digits in a column

1 Answers1