combine first name and last name

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)

combine first name and last name

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2011-10-12 : 12:03:19

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

52326 Posts

Posted - 2011-10-12 : 12:24:20

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/

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-10-12 : 12:28:27

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 12:30:12

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

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

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-10-12 : 12:32:22

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:07:43

try coalesce

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

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

ntn104
Posting Yak Master

175 Posts

Posted - 2011-10-14 : 23:30:04

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/

Kristen
Test

22859 Posts

Posted - 2011-10-15 : 03:19:40

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

ntn104
Posting Yak Master

175 Posts

Posted - 2011-11-01 : 16:19:02

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-11-02 : 22:31:40

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.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources