November 18, 2009 at 2:44 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
November 18, 2009 at 3:00 am
SELECT SomeColumn
FROM SomeTable
WHERE LEN(SomeColumn) > 1
-- Gianluca Sartori
November 18, 2009 at 3:10 am
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
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