Posted by Rad on 09/15/05 02:55

I have a table . It has a nullable column called AccountNumber, which
is of varchar type. The AccountNumber is alpha-numeric. I want to take
data from this table and process it for my application. Before doing
that I would like to filter out duplicate AccountNumbers. I get most of
the duplicates filtered out by using this query:

select * from customers
where AccountNumber NOT IN (select AccountNumber from customers where
AccountNumber <> '' group by AccountNumber having count(AccountNumber)
> 1)

But there are few duplicate entries where the actual AccountNumber is
same, but there is a trailing space in first one, and hence this
duplicate records are not getting filtered out. e.g
"abc123<white-space>" and "abc123" are considered two different entries
by above query.

I ran a query like :

update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)

But even after this query, the trailing space remains, and I am not
able to filter out those entries.

Am I missing anything here? Can somebody help me in making sure I
filter out all duplicate entries ?

Thanks,
Rad

[Back to original message]