Select Distinct Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Distinct Issue

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-05-01 : 12:01:05

Hi there

We have a SQL Server 2005 database (outside of my control).

If i do a select distinct...


select distinct
	ltrim(rtrim(lower(userEmail)))			AS userEmail,
from tbl_users
order by userEmail asc

i get duplicate emails showing up, so i f i do...


select distinct
	ltrim(rtrim(lower(userEmail)))			AS userEmail,
	ascii(right(useremail,1))
from tbl_users
order by userEmail asc

i can see that a lot of them have trailing carriage returns (amongst a few other things) which is i think why the distinct is seeing them as just that.

Question:
Is there someway to do a distinct select on this data and ignore any potential trailing ascii chars such as (carriage return - 13)?

i cannot alter the underlying data which is beyond my control.

Any ideas a great help.

====
Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:06:01

[code]
select distinct
replace(ltrim(rtrim(lower(userEmail))),char(13),'') AS userEmail,
from tbl_users
order by userEmail asc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-01 : 12:07:47

you can use the REPLACE function to get rid of the junk at the end:
REPLACE(REPLACE(useremail,char(10),''),char(13),'') will get rid of a lot of it.

Jim

Everyday I learn something that somebody else already knew

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-05-01 : 12:26:35

Great - thanks guys

====
Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:28:03

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources