Post reply

How to eliminate bad data

  • November 18, 2009 at 2:44 am

    #139455

    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

  • November 18, 2009 at 3:00 am

    #1080754

    SELECT SomeColumn

    FROM SomeTable

    WHERE LEN(SomeColumn) > 1

    -- Gianluca Sartori

  • November 18, 2009 at 3:10 am

    #1080765

    Thanks, I'd totally forgotten about LEN

    How do you structure it if you want

    where LEN (firstname, surname, addressline etc) > 1

    ?

    Thanks

  • November 18, 2009 at 3:28 am

    #1080774

    It could be:

    SELECT SomeColumn

    FROM SomeTable

    WHERE LEN(Name) > 1

    AND LEN(Address) > 1

    AND LEN(Phone) > 1

    ... etc

    It depends on what you want to achieve. If you set those conditions in the where clause, it could not retrieve a row just because one column contains invalid data. If you want the row anyway, but don't want the bad data, use CASE functions in the select clause:

    SELECT CASE WHEN LEN(Name) > 1 THEN Name ELSE NULL END,

    CASE WHEN LEN(Address) > 1 THEN Address ELSE NULL END,

    CASE WHEN LEN(Phone) > 1 THEN Phone ELSE NULL END,

    ... etc

    FROM SomeTable

    WHERE ID = @id

    Regards

    Gianluca

    -- Gianluca Sartori

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply