Concat separate name fields into one

Question

Post reply

Concat separate name fields into one

J Bagwell

Mr or Mrs. 500

Points: 514
More actions
June 30, 2004 at 11:36 am

#108422

I'm certain many folks have run into this and that there is a solution out there, but I can't seem to find it.
I have 5 fields that make up a person's name. Prefix, FirstName, MiddleName, LastName, and Suffix. I need to concatenate them together into two full name fields, FirstLastName and LastFirstName.
Simple string concatenation is easy, but I need to allow for any or all of the fields being empty or null, I need to put spaces and/or commas between them appropriately, and I don't want 2 spaces together nor a space at the beginning. And I need to do it within a View, ie a Select Statement.
I can do it brute force, but it's going to be very ugly. Does anyone have an elegant and compact solution?
J. Bagwell
UVA Health System

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Antares686

SSC Guru

Points: 125444

June 30, 2004 at 11:49 am

#512723

Look at the IsNull function.

J Bagwell Mr or Mrs. 500 Points: 514 More actions · Answer 2

Yes, IsNull helps a lot. However, when it comes to putting spaces between the fields and not end up with 2 spaces, it gets a little dicey.

For Example:

Prefix: empty or null

FirstName: Jana

Middle Name: empty or null

LastName: Bagwell

Suffix: empty or null

I don't want to end up with ' Jana Bagwell '

I want 'Jana Bagwell'

Thanks.

J. Bagwell

UVA Health System

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Why not create the FirstName and LastName separate and then do the following:

UserName = RTRIM(LTRIM(@FirstName)) + SPACE(1) + RTRIM(LTRIM(@LastName))

Good Hunting!

AJ Ahrens

webmaster@kritter.net

J Bagwell Mr or Mrs. 500 Points: 514 More actions · Answer 4

I'm not sure what you mean about creating them separately. I want my FirstLastName to look like:

'Mr. Jeff L Bagwell, Jr' or

'Mr. Jeff L Bagwell' or

'Mr. Jeff Bagwell, Jr' or

'Jeff L Bagwell'

Then the LastFirstName would look like:

'Bagwell, Mr. Jeff L' or

'Bagwell, Mr. Jeff' etc....

J. Bagwell

UVA Health System

hoo-t SSChampion Points: 12713 More actions · Answer 5

declare

@Prefix varchar(10),

@FirstName varchar(10),

@MiddleName varchar(10),

@LastName varchar(10),

@Suffix varchar(10),

@First_Group varchar(50),

@Last_Group varchar(50),

@FirstLastName varchar(100),

@LastFirstName varchar(100)

set @Prefix = ''

set @FirstName = 'Jeff'

set @MiddleName = 'L'

set @LastName = 'Bagwell'

set @Suffix = 'Jr'

set @First_Group =

rtrim(isnull(@Prefix,''))

+ ' '

+ rtrim(isnull(@FirstName,''))

+ ' '

+ rtrim(isnull(@MiddleName,''))

set @Last_Group =

rtrim(isnull(@LastName,''))

+ ', '

+ rtrim(isnull(@Suffix,''))

if substring(reverse(rtrim(@Last_Group)),1,1) = ','

set @Last_Group = replace(@Last_Group,',','')

set @FirstLastName = rtrim(isnull(@First_Group,'')) + ' ' + rtrim(isnull(@Last_Group,''))

set @FirstLastName = replace(@FirstLastName,' ',' ')

if substring(@FirstLastName,1,1) = ' '

set @FirstLastName = ltrim(@FirstLastName)

print @FirstLastName

set @LastFirstName = rtrim(isnull(@Last_Group,'')) + ', ' + rtrim(isnull(@First_Group,''))

set @LastFirstName = replace(@LastFirstName,' ',' ')

print @LastFirstName

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Jana,

Here's a real simple solution using the power of a NULL being returned when a NULL is concatenated... the key is to concatenate each space or comma with each name part and then doing an ISNULL.

Here's the data I used...

FullNamesID Prefix FirstName MiddleName LastName Suffix

----------- ------ --------------- --------------- --------------- ---------------

1 Mr. Jeff L. Bagwell Jr.

2 NULL Jeff NULL Bagwell NULL

3 NULL Jeff Lawrence Bagwell NULL

4 Mr. Jeff NULL Bagwell NULL

5 Mr. Jeff NULL Bagwell Jr.

(5 row(s) affected)

Here's the results...

FirstLastName LastFirstName

------------------------------ ------------------------------

Mr. Jeff L. Bagwell, Jr. Bagwell, Mr. Jeff L., Jr.

Jeff Bagwell Bagwell, Jeff

Jeff Lawrence Bagwell Bagwell, Jeff Lawrence

Mr. Jeff Bagwell Bagwell, Mr. Jeff

Mr. Jeff Bagwell, Jr. Bagwell, Mr. Jeff , Jr.

(5 row(s) affected)

And here's the code (I used "FullNames" as the table name)...

SELECT
ISNULL(Prefix+' ','') +ISNULL(FirstName+' ','') +ISNULL(MiddleName+' ','') +ISNULL(LastName,'') +ISNULL(', '+Suffix,'') AS FirstLastName,

ISNULL(LastName+', ','') +ISNULL(Prefix+' ','') +ISNULL(FirstName+' ','') +ISNULL(MiddleName,'') +ISNULL(', '+Suffix,'') AS LastFirstName
FROM FullNames

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Jana,

Yeah, before you say anything, I see the bug, too. Here's the corrected code... had to assume that you would have something in first name and last name fields to make it work...

SELECT LEFT(

ISNULL(Prefix+' ','')

+ISNULL(FirstName+' ','')

+ISNULL(MiddleName+' ','')

+ISNULL(LastName,'')

+ISNULL(', '+Suffix,''),30)

AS FirstLastName,

left(ISNULL(LastName+', ','')

+ISNULL(Prefix+' ','')

+ISNULL(FirstName,'')

+ISNULL(' '+MiddleName,'')

+ISNULL(', '+Suffix,''),30)

AS LastFirstName

FROM FullNames

And here's what the new output looks like...

FirstLastName LastFirstName

------------------------------ ------------------------------

Mr. Jeff L. Bagwell, Jr. Bagwell, Mr. Jeff L., Jr.

Jeff Bagwell Bagwell, Jeff

Jeff Lawrence Bagwell Bagwell, Jeff Lawrence

Mr. Jeff Bagwell Bagwell, Mr. Jeff

Mr. Jeff Bagwell, Jr. Bagwell, Mr. Jeff, Jr.

(5 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Bagwell Mr or Mrs. 500 Points: 514 More actions · Answer 8

Jeff,

Great idea! Thanks. I think that takes care of my problem quite nicely.

Jana

J. Bagwell

UVA Health System