Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,906
    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.

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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,422
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2012
    Posts
    13
    Quote Originally Posted by healdem View Post

    So, you don't actually know.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,906
    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.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,422
    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 on the Tiger 800 or the Norton

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

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

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,422
    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 on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,906
    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.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,906
    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.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,422
    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
    I'll show you mine, if you show me yours
    I'd rather be riding on the Tiger 800 or the Norton

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

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •