locked
How to eliminate bad data RRS feed

  • Question

  • Hi

    I have a customer table which is full of bad data, but does have some good data in it.

    The bad data is where the operator has just entered 1 or 0's (any single character) instead of name, address, postcode etc.

    How do I query this table to retrieve only the good data ie select name, address, postcode etc from c_table where id = x but don't return any value with only 1 character?

    Thanks
    Craig
    Wednesday, November 18, 2009 10:01 AM

Answers


  •  
       select name, address, postcode  from  c_table where id=X and len(name)!=1 and len(address)!=1 and len(postcode)!=1
    Lakshman
    • Proposed as answer by Yogesh Bhadauriya Wednesday, November 18, 2009 10:14 AM
    • Marked as answer by SQLLQS Wednesday, November 18, 2009 10:34 AM
    Wednesday, November 18, 2009 10:07 AM
  • Hi

    I have a customer table which is full of bad data, but does have some good data in it.

    The bad data is where the operator has just entered 1 or 0's (any single character) instead of name, address, postcode etc.

    How do I query this table to retrieve only the good data ie select name, address, postcode etc from c_table where id = x but don't return any value with only 1 character?

    Thanks
    Craig

    select name, address, postcode  
    from  c_table
    where id=X
    and len(name)>1 and len(address)>1 and len(postcode)>1


    Who Care! Who Regret!
    • Proposed as answer by Yogesh Bhadauriya Wednesday, November 18, 2009 10:14 AM
    • Marked as answer by SQLLQS Wednesday, November 18, 2009 10:34 AM
    Wednesday, November 18, 2009 10:14 AM

All replies


  •  
       select name, address, postcode  from  c_table where id=X and len(name)!=1 and len(address)!=1 and len(postcode)!=1
    Lakshman
    • Proposed as answer by Yogesh Bhadauriya Wednesday, November 18, 2009 10:14 AM
    • Marked as answer by SQLLQS Wednesday, November 18, 2009 10:34 AM
    Wednesday, November 18, 2009 10:07 AM
  • Hi

    I have a customer table which is full of bad data, but does have some good data in it.

    The bad data is where the operator has just entered 1 or 0's (any single character) instead of name, address, postcode etc.

    How do I query this table to retrieve only the good data ie select name, address, postcode etc from c_table where id = x but don't return any value with only 1 character?

    Thanks
    Craig

    select name, address, postcode  
    from  c_table
    where id=X
    and len(name)>1 and len(address)>1 and len(postcode)>1


    Who Care! Who Regret!
    • Proposed as answer by Yogesh Bhadauriya Wednesday, November 18, 2009 10:14 AM
    • Marked as answer by SQLLQS Wednesday, November 18, 2009 10:34 AM
    Wednesday, November 18, 2009 10:14 AM