trim (?) and distinct functions

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 2000 Forums

Transact-SQL (2000)

trim (?) and distinct functions

Author

Topic

decem
Starting Member

21 Posts

Posted - 2005-05-26 : 14:36:14

hi guys.. two quick, and i'm sure easy, questions:

1. column one has last name.. column two has first name.. i want to join these so i have one column that shows first twelve letters of lastname+firstname (if c.1 = smith and c.2 = john, then c.3 = smithjohn). for the most part i have that covered w/ this:

LOWER(LEFT(dbo.vwUsers_EHD.Lastname + dbo.vwUsers_EHD.firstname, 12)) AS uname

one problem.. the lastname field includes suffixes (smith jr.).

how do i trim off the space and the jr. and then add on the first name in such instances?

2. i can use the distinct function to eliminate duplicates.. but how can i return the duplicates to see what they are?

jhermiz

3564 Posts

Posted - 2005-05-26 : 14:39:21

1) TRIM() gets rid of spaces

2) That is an odd question, if you just said you used distinct to eliminate duplicates and now you say "but how can i return the duplicates to see what they are"...umm dont use distinct ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

decem
Starting Member

21 Posts

Posted - 2005-05-26 : 14:44:34

thanks jhermiz.. as to the distinct.. i have 40,133 results if use distinct.. 40,162 if not.. i want to know what those 29 are.. but i don't want to have to wade through them to find out.

jhermiz

3564 Posts

Posted - 2005-05-26 : 14:53:01

ok think of it like this...
you have one query returning 50 rows
you have another query returning 53 rows
you expected both queries to return 50 however you got 3 more right...
so you know one thing, you know that the 50 must be the same but you wonder what the 3 are
so what you do is join both queries using a right join.

try to come up with a solution and post it here and we will help you fix it.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

decem
Starting Member

21 Posts

Posted - 2005-05-26 : 14:55:27

another problem w/ trim.. obviously the last names that have some sort of suffix are going to be different lengths, i might have a smith jr. and hernandez sr., but it looks as though trim only works to set a specified length before trimming it.. that won't work.

decem
Starting Member

21 Posts

Posted - 2005-05-26 : 15:14:09

two tables.. one pulling from HR's db, another from AD..
each user is given a uid..
so i pull all data from the HR db, joining it with the AD and pulling from 4 fields there where the uid = uid..
so really, the duplicate uid's must already exist in the HR db..
where do i go from there?

i'm not making sense am i? or am i?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-26 : 15:28:52

quote:
Originally posted by decem

where do i go from there?

By giving us the appropriate info so we don't have to guess...

Follow the instructions here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Brett

8-)

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-27 : 12:02:20

Are you looking for something like this??

declare @name table (name_id int, first_name varchar(20), last_name varchar(20))
insert into @name 
	select 1, 'Nathan', 'Skerl' union
	select 2, 'Adam', 'Skerl Jr' union
	select 3, 'John', 'Anthony Skerl Jr'

select	name_id,
	first_name,
	last_name,
	left((last_name),len(last_name)-charindex(' ',reverse(last_name),1)) as last_name_mod,
	left((last_name),len(last_name)-charindex(' ',reverse(last_name),1)) + ', ' + first_name as formatted_name
from @name

- nathan skerl

decem
Starting Member

21 Posts

Posted - 2005-05-27 : 12:28:25

Thanks Brett.. sorry about that. Here's what i have so far:

select
v.*,
a.ssn as userid,
a.vip,
v.firstname + ' ' + v.Lastname AS FullName,
LOWER(LEFT(v.Lastname + v.firstname, 12)) AS username,
a.homedir,
right (a.exchangeserver, 11) as exchangesvr,
a.streetaddress,
a.city,
a.state,
a.country,
a.location,
a.zip

from vwusers_ehd as v
Left Outer Join adusers as a
ON
v.userid = a.ssn and
v.lastname = a.lastname and
v.firstname = a.firstname and
v.email = a.email

So.. I figured out the duplicate issue.. I just needed to join it on more columns.

Now the only problem I'm having is with the username column that i'm trying to create.
I want to take the first 12 letters of last name + first name, so john smith should be smithjohn, billy francesca should be francescabil, etc.

LOWER(LEFT(v.Lastname + v.firstname, 12)) works just fine for the most part, until there's a suffix..

If there's is a john smith jr, then this returns "smith jrjoh", which I can't have.

How can I fix that?

Thanks again for your patience guys..

decem
Starting Member

21 Posts

Posted - 2005-05-27 : 12:48:08

Oops.. sorry Nathan.. I must have started my post before you replied..

Your post/statement is over my head... I have no experience at all with this stuff.. I've never used the len, reverse or charindex commands. I just looked them up and understand them individually, but I don't understand how they would all combine and what result would come from the way you applied them. Could you tell me more about how this would work or could you use my statement from above and apply it to that?

decem
Starting Member

21 Posts

Posted - 2005-05-31 : 11:12:23

bumping back up to the top..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources