SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 combine first name and last name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ntn104
Posting Yak Master

172 Posts

Posted - 10/12/2011 :  12:03:19  Show Profile  Reply with Quote
Hello,

Please help me to write sql statement to clean up name and put that in the same field.

I have two fields 1) FIRSTNAME, 2) LASTNAME
I want to combine first name and last name as name ((SELECT FIRSTNAME + LASTNAME) AS NAME)
But space between first name and lastname....for example result came out like: ANN D FURLENG. I want like ANN D. FURLENG.

tHANKS,

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/12/2011 :  12:24:20  Show Profile  Reply with Quote
when you want . and when you want space? is it based on whether you've initials in between?

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

Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts

Posted - 10/12/2011 :  12:28:27  Show Profile  Reply with Quote
Actually the firsname field sometimes it display firstname and middle initial (like below example)...plus space behind that...

so when I used select isnull(firstname,'') + isnull(lastname,'')) AS FullName, then I got a lot space between firstname and lastname (forexample, several space between D and PHANEUF as below example)
but I want like
ANDREA D PHANEUF

Thanks,





quote:
Originally posted by visakh16

when you want . and when you want space? is it based on whether you've initials in between?

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




Edited by - nt4vn on 10/12/2011 12:30:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/12/2011 :  12:30:12  Show Profile  Reply with Quote
sorry it looks same. can you tell whats the difference?

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

Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts

Posted - 10/12/2011 :  12:32:22  Show Profile  Reply with Quote
sorry when I copied from my table to here...it looks the same...but it had more space between first and last name.....I want to get rid of those space...I used (rtrim(firstname) + lastname) but it show like

ANDREA DPHANEUF


quote:
Originally posted by visakh16

sorry it looks same. can you tell whats the difference?

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



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/12/2011 :  14:07:43  Show Profile  Reply with Quote
try coalesce

select coalesce(firstname,'') + coalesce(lastname,'') AS FullName

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

Go to Top of Page

ntn104
Posting Yak Master

172 Posts

Posted - 10/14/2011 :  23:30:04  Show Profile  Reply with Quote
Using coalesce still not work as desire...so I used the following:
select (rtrim(firstname)+ ' ' + trim(lastname)) as fullname

I have to add a space between the single quote ('') in order to work.

Thanks for all the help!


quote:
Originally posted by visakh16

try coalesce

select coalesce(firstname,'') + coalesce(lastname,'') AS FullName

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



Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/15/2011 :  03:19:40  Show Profile  Reply with Quote
I use

COALESCE(firstname + ' ', '') + COALESCE(lastname, '')

which allows for the fact that either column may be null (which can be the case in our database, and we do not store an empty string in either column).

If you could have NULL or an Empty String then you could use this Belt & Braces approach:

RTRIM(COALESCE(NullIf(RTrim(firstname), '') + ' ', '') + COALESCE(lastname, ''))

SELECT	ID, RTRIM(COALESCE(NullIf(RTrim(firstname), '') + ' ', '') + COALESCE(lastname, '')) + '['
FROM
(
	SELECT	1 AS ID, 'MyFirst' AS firstname, 'MyLast' AS lastname UNION ALL
	SELECT	2, '', 'MyLast' UNION ALL
	SELECT	3, 'MyFirst', '' UNION ALL
	SELECT	4, NULL, 'MyLast' UNION ALL
	SELECT	5, 'MyFirst', NULL UNION ALL
	SELECT	6, 'MyFirst X', 'MyLast' UNION ALL
	SELECT	7, 'MyFirst              ', 'MyLast            '
) AS X

Edited by - Kristen on 10/15/2011 03:23:24
Go to Top of Page

ntn104
Posting Yak Master

172 Posts

Posted - 11/01/2011 :  16:19:02  Show Profile  Reply with Quote
Kristen,

This works perfectly....thanks much!

quote:
Originally posted by Kristen

I use

COALESCE(firstname + ' ', '') + COALESCE(lastname, '')

which allows for the fact that either column may be null (which can be the case in our database, and we do not store an empty string in either column).

If you could have NULL or an Empty String then you could use this Belt & Braces approach:

RTRIM(COALESCE(NullIf(RTrim(firstname), '') + ' ', '') + COALESCE(lastname, ''))

SELECT	ID, RTRIM(COALESCE(NullIf(RTrim(firstname), '') + ' ', '') + COALESCE(lastname, '')) + '['
FROM
(
	SELECT	1 AS ID, 'MyFirst' AS firstname, 'MyLast' AS lastname UNION ALL
	SELECT	2, '', 'MyLast' UNION ALL
	SELECT	3, 'MyFirst', '' UNION ALL
	SELECT	4, NULL, 'MyLast' UNION ALL
	SELECT	5, 'MyFirst', NULL UNION ALL
	SELECT	6, 'MyFirst X', 'MyLast' UNION ALL
	SELECT	7, 'MyFirst              ', 'MyLast            '
) AS X


Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/02/2011 :  22:31:40  Show Profile  Reply with Quote
Considering the frequency of requests we get for helping in DIVIDING name strings into their components, it seems a bit perverse to be providing assistance on how to combine them....
You really should not be storing name components as a single string. Combine it at the presentation level, not the database level. You're going to kick yourself later when you get tasked with searching by first name or last name.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000