By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,978 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,978 IT Pros & Developers. It's quick & easy.

Removing white spaces in a varchar column

P: n/a
Rad
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

Sep 15 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Stu
Are you sure it's a whitespace? It might be a line break. To verify,
you might try something like:

SELECT AccountNumber
FROM customers
WHERE AccountNumber LIKE '%" + CHAR(10)
OR AccountNumber LIKE '%' + CHAR(13)

Oh, and always be cautious about running an UPDATE statement with no
WHERE clause.

HTH,
Stu

Sep 15 '05 #2

P: n/a
Rad
Thanks Stu, you made my day. :)

It was a line break after a white space. I was concentrating only on
the white space and did not notice the line break. So in my first
statement, I first use this query:

UPDATE customers
SET AccountNumber = substring(AccountNumber, 1, PATINDEX('CHAR(13)',
AccountNumber))
WHERE AccountNumber like '%' + CHAR(13)

and it worked!

Thanks again!

Regards,
Rad

Stu wrote:
Are you sure it's a whitespace? It might be a line break. To verify,
you might try something like:

SELECT AccountNumber
FROM customers
WHERE AccountNumber LIKE '%" + CHAR(10)
OR AccountNumber LIKE '%' + CHAR(13)

Oh, and always be cautious about running an UPDATE statement with no
WHERE clause.

HTH,
Stu


Sep 15 '05 #3

P: n/a
>> table . It has a nullable column called AccountNumber, which is of VARCHAR(n) type. <<

That is a very bad code design because it prevents check digits and
makes validation rules more complex. If you had validation rules in the
DDL, you would not have this problem. First mop the floor, then fix
the leak.

Sep 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.