If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Corrupt Data - Find Good Records

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 13
Corrupt Data - Find Good Records

I am working on a database where a particular column data has been corrupted by people entering things like "No longer available".

This column is supposed to contain product part numbers.

I need a query that will return all records where this column contains:

numbers
numbers and letters and other characters such as dashes

The query must exclude all records that do not contain any numbers at all.

Valid examples:

19987
665.43
abc123
def-1209-ea

I am guessing this can probably done with regex.

Can anybody help?
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,448
A better solution would be to find all values that do NOT exist in your product table. This is an easier query to write, and produces better results.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 13
I specifically need to find these records, not the opposite.

Just to clarify - the query is for a maintenance script, not production, so whatever you may think is "better" does not apply.

Last edited by FunkyBoy; 11-26-12 at 13:46.
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,662
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 13
Quote:
Originally Posted by healdem View Post

So, you don't actually know.
Reply With Quote
  #6 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,448
Quote:
Originally Posted by FunkyBoy View Post
So, you don't actually know.
Let's see... You are willing to do more work than necessary to get a poorer quality answer. That leaves me with one more question: When is your assignment due?

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,662
Quote:
Originally Posted by FunkyBoy View Post
So, you don't actually know.
nasty attitude you have there matey.

Yes I do know, and when I don't know my first point of call is da manuel, or examples on t'web. If I need assistance with how to (and clearly you don't as you've already identified regex as your solution) then I'd post a questions here. Or if Im struggling with a problem I'd post what I'd done and ask what I was doing wrong.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 13
Quote:
Originally Posted by Pat Phelan View Post
Let's see... You are willing to do more work than necessary to get a poorer quality answer. That leaves me with one more question: When is your assignment due?

-PatP
Which means exactly what?

Do you two get a thrill feeling superior and trolling people who are looking to understand something?

I am sure there are many things that I know far more about than you do - you have not even demonstrated that you actually know very much about MySQL even - you have not been able to provide any kind of answer other than smart alek trolling.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 13
Quote:
Originally Posted by healdem View Post
nasty attitude you have there matey.

Yes I do know, and when I don't know my first point of call is da manuel, or examples on t'web. If I need assistance with how to (and clearly you don't as you've already identified regex as your solution) then I'd post a questions here. Or if Im struggling with a problem I'd post what I'd done and ask what I was doing wrong.
See my post above, it applies to you also.

Yes, I am aware that regex is probably the key and yes, I have looked at the manual. Regex is a notoriously difficult subject and if YOU are so expert with it you would be able to provide an answer. Clearly you are far from an expert and get your jollies trolling on internet forums - get a life please.
Reply With Quote
  #10 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,662
interesting approach you have matey...
reporting the site Admin and one of the moderators as trolls.

what have you tried
what isn't working

the site gets a lot of people wanting solutions to their homework or their whatever. There's now law against that, but as a general rule this site like to see you make an effort as opposed to demanding a solution.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #11 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,448
Quote:
Originally Posted by FunkyBoy View Post
Which means exactly what?
My proposed solution (comparing the data in your column against the known good data from the product id column in the product table) is easier to do and provides a more useful result. It is an exact, and completely correct answer as opposed to the list of potentially correct answers that would be provided by a regular expression.

To summarize that again: My suggestion is an easier solution that provides a better quality result.
Quote:
Originally Posted by FunkyBoy View Post
Do you two get a thrill feeling superior and trolling people who are looking to understand something?
Not at all. We do the best that we can to help people, even the ones that resist help Based on your behavior so for, you need a lot more help than most DBForums users and we're even trying to help you. If you let us, we will help you... If you adamantly refuse help, we're ok with that too.
Quote:
Originally Posted by FunkyBoy View Post
I am sure there are many things that I know far more about than you do - you have not even demonstrated that you actually know very much about MySQL even - you have not been able to provide any kind of answer other than smart alek trolling.
I'm sure that you do know a great deal more about many things than I do, and I'm Ok with that. Based on the questions that you are asking and the behavior that you are exhibiting, it seems a pretty safe bet that databases and life in general are subjects where I have a bit more experience than you do.

I'm still happy to help you if you want it. I don't think that you'll find anyone here at DBForums that will do your homework for you, but you'll find many folks that can (and usually will) give you a hand!

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #12 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,448
Quote:
Originally Posted by healdem View Post
interesting approach you have matey...
reporting the site Admin and one of the moderators as trolls.

what have you tried
what isn't working

the site gets a lot of people wanting solutions to their homework or their whatever. There's now law against that, but as a general rule this site like to see you make an effort as opposed to demanding a solution.
I got a chuckle from that report too.

I have a gut feel that this one can be helped, but it will be a challenge for us.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #13 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,662
just for the hell of it I created a table, stuffed some data in
followed da manuel (yes it was the reference cited)
and got the REGEXP expression to work within 30 seconds or so that time being purely to get the select using regexp to work.

....so, gwan matey, have a try, and lets see what where you are struggling

as they used to say in my day at school behind the bike sheds
Quote:
I'll show you mine, if you show me yours
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 13
Ok, so now things are very clear. You guys have been here so long that you have become too jaded to actually help people and too superior minded also. Yes, what you are doing IS trolling.

Neither of you have offered any help whatsoever.

Pat Phelan - you insist that I should do something that does not in any way accomplish what I NEED - I have stated clearly what the requirement is and that possibly regex is the solution. You offer this (without an actual solution):

"My proposed solution (comparing the data in your column against the known good data from the product id column in the product table) is easier to do and provides a more useful result."

Comparing my data with the KNOWN good data? This is the thing - I DO NOT KNOW what the good data is and THAT is what I am trying to find. I know the CONDITIONS of what the good data looks like, but do NOT know how to form a query for it. The products_id column has very little to do with anything.

Honestly, if you guys cannot offer people actual help rather than insults, please do not bother them at all.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 13
Quote:
Originally Posted by healdem View Post
just for the hell of it I created a table, stuffed some data in
followed da manuel (yes it was the reference cited)
and got the REGEXP expression to work within 30 seconds or so that time being purely to get the select using regexp to work.

....so, gwan matey, have a try, and lets see what where you are struggling

as they used to say in my day at school behind the bike sheds

And so you prove my point - you are a dead set troll.
Reply With Quote
Reply

Tags
regex

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On