How to group rows by the numeric characters in a string field?

Question

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.:

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

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.:

PRODUCT_ID    GROUP
----------    -----
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?

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
– thrau, Commented Apr 2, 2012 at 0:27
After looking at that answer, I agree that the SQL solution would not be elegant. Modified question. — Belmin Fernandez
– Belmin Fernandez, Commented Apr 2, 2012 at 0:30

ruakh · Accepted Answer · 2012-04-02 00:41:50Z

3

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 ...

answered Apr 2, 2012 at 0:41

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Li-aung Yip Over a year ago

Hermes: "Sweet type coercion of Jupiter!" +1 for completely unintuitive but efficient solution.

mgilson · Accepted Answer · 2012-04-02 00:33:52Z

2

this is a perfect place for regex...

import re
RE=re.compile(r'\d+')
#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.

EDIT

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.

answered Apr 2, 2012 at 0:33

mgilson

312k70 gold badges656 silver badges722 bronze badges

Collectives™ on Stack Overflow

How to group rows by the numeric characters in a string field?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related