Link to home
Create AccountLog in
Avatar of theideabulb
theideabulbFlag for United States of America

asked on

Looking for groups of numbers to omit using CF or mySQL

I am looking for product listings that contain multiple product numbers, but disregards years

So if there is a 4 digit year from say 1970-2013, allow those numbers, but the following listings would be "good".   These are in german so just bear with it.

lego duplo flughafen super set  7840 mit originalverpackung und anleitung
lego magikus 3836
lego robo champ 3835
lego lava dragon 3838
lego race 3000 3839
lego city  großer baukran 7905
lego 8297 technic großer geländewagen ovp komplett top

This would be good as well, because it has 2012 in it and thats allowed:
lego technik 9397 holztransporter neuheit 2012

These would be "bad" since they have multiple numbers
lego belville rutsche 4876 fabuland 6738 r3
lego treppe 4784 6273 6270 old dark gray r3
riesiges lego set polizei hauptquartier ovp 7035 7034 7033 7031 7032
lego 32018: 2 neu-hellgraue lochsteine 1x14 10179 10134 7784 4504 7261 7905
lego 3894: 10 neu-dunkelgraue lochsteine 1x6 aus 10179 10221 6753 8039 8098 4504
32140 lego 4 weiße 2x4 liftarme dick technik 8071 8009 7931 10179 8289 8252 neu

So basically I want to mark any of these that have multiple numbers.  It would have to have at least 4 digits.  Any suggestions to do this via query or cf would be appreciated.
Avatar of _agx_
Flag of United States of America image

You may need a mySQL regex (which is not my area) for this task.  I'd suggest adding "regex" and "mysql" to the question tags. If you can't do it yourself, submit a Request for Attention.
Avatar of theideabulb


Thank you, i asked for help to add those groups in
Are those strings part of the same column or they span over multiple columns?

Another question is what year numbers are acceptable; i.e 1967 is it good?

Another: how do you know if that number actually is a year and not just a product code?
Yes all from the same column.   Each line is its own record.   Yes 1867 can be a year.   I am just trying like a 98% figure that it actually won't be there.   This will help cut down on mist if the garbage data.
I Understand but you still have to determine what is the range of numbers that you would count as years, like should it start from 1753 up to 2013? What are the boundaries?
Sorry I missed the 1970 to 2012 from your question.
You gave this example as good:

lego race 3000 3839

Is that the case?

Anyway the solution I see here is to create a function that will actually determine if a string contains more than one set of numbers but only after you eliminate all the years from it. It is not trivial but doable.

One more question; are all the numbers 4 digits?
Ok, yes, you caught that, that was supposed to be removed or be labeled as a bad row.

The numbers can be more than 4,  mostly 4 and 5 and that would solve most of my problem.

In the end, I am not sure if there is an overall perfect solution, I am just trying to flag more rows of data.
If you want to solve this with MySQL then you need a user defined function to check if there is more than one group of numbers in the string after you stripped out any possible year. Other that that you can create an executable to loop through the table and do the same.
I've requested that this question be deleted for the following reason:

Everything related to this question is no longer needed.  The client wound up getting different sets of data, so I really never found an answer to award someone.  Thank you for your assistance.
Avatar of Zberteoc
Flag of Canada image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I have no idea what the real answer was since I never got to finish that project.  I was trying to do the right thing and not pick an answer because I don't know what it is.  I hope you are happy with the answer now.  Sorry I could not be of better help with this but I appreciate the effort.