Take the 2-minute tour ×

I have some very ugly data I am trying to massage. It consist of SKUs and I want to group them into product line. E.g.:


Basically, I want to group items by the first numeric characters in the PRODUCT_ID field. I.e., all the characters up to the first non-numeric character. E.g.:

313L30WHITE   313
313L40WHITE   313
313L30BLACK   313
3333          3333
2L10RED       2
2L20BLACK     2
32341/30/BLK  32341

Seems like a SQL solution would not be elegant. Because of that, I would prefer a Python solution that creates a new table with a new GROUP column.

Anyone have any suggestions?

share|improve this question
solving this in mysql is rather unclean in my opinion. but a question regarding the more general issue of extracting numbers from strings in a mysql query was asked here: stackoverflow.com/questions/978147/… –  thrau Apr 2 '12 at 0:27
After looking at that answer, I agree that the SQL solution would not be elegant. Modified question. –  Belmin Fernandez Apr 2 '12 at 0:30

2 Answers 2

up vote 3 down vote accepted

If you know that PRODUCT_ID will always start with one or more numeric characters, then you can just convert it to a number by adding 0:

select PRODUCT_ID,
       0 + PRODUCT_ID as GROUP
  from ...

See §11.2 "Type Conversion in Expression Evaluation" in the MySQL 5.6 Reference Manual.

If you want GROUP to be textual rather than numeric, then you can write:

select PRODUCT_ID,
       concat(0 + PRODUCT_ID) as GROUP
  from ...
share|improve this answer
Hermes: "Sweet type coercion of Jupiter!" +1 for completely unintuitive but efficient solution. –  Li-aung Yip Apr 2 '12 at 11:18

this is a perfect place for regex...

import re
#Set up the list of SKU's
List_of_SKUs.sort(key=lambda x:int(RE.match(x).group()))

Now your list is sorted.

The regex just pulls off the longest integer at the start of the string. The lambda function just accesses that portion of the string and casts it to an integer which is used for sorting.


From there, if you want to print the table, you could do something like:

for item in List_of_SKUs:
    print "%s\t%s"%(item,RE.match(item).group())

Although there is probably a more efficient way of doing this.

share|improve this answer

Your Answer


