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

Transact-SQL (2000)

String Concatenation

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-10-14 : 01:51:54

I have a select statement like the following:

Select
(IsNull(LastName,'') + ', ' + IsNull(FirstName,'')) as LastNameFirstName
From
Table

Results:
Smith, John
Jackson, Larry

The annoying problem that I cannot figure out how to handle is if there is no first name. We have many entries that only have a LastName such as ABC Company. The problem is that it looks like:

ABC Company,

I cannot figure out how to not display the , if the first name is blank. This is a very simplified version of what I am doing, so I am thinking if I can figure this name issue out, then I can use the same for NameSuffixes, Address issues, etc.

Thank you thank you thank you! I refer to this website and this forum many times for my issues and your advice is always a huge benefit.

Nazim
A custom title

1408 Posts

Posted - 2003-10-14 : 02:43:59

Select
CASE IsNull(FIRSTNAME,',') WHEN ',' THEN LASTNAME ELSE LASTNAME +',' END +
IsNull(FIRSTName,'') AS LastNameFirstName
From #TEST

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-14 : 02:53:22

SELECT IsNull(lastname, '') + isnull(',' + firstname, '') FROM1 #names

But what do you want to when both are null?

Owais

Make it idiot proof and someone will make a better idiot

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-10-14 : 11:19:41

LastName is a required field, so LastName will never be null. However, in the off-chance that somehow it is Null, I do not want to display that person at all.

Thanks for both suggestions. I never can seem to get a handle on these case statements within a select statement. I had never even thought of using the comma with the following field. duh!

TimChenAllen
Starting Member

45 Posts

Posted - 2003-11-21 : 04:49:25

Here's a SQL statement that handles all the possible cases. When doing this, it's a good idea to make a little table of possiblities and what you want in each case, e.g.:

FirstName   LastName   Desired Result
NULL        NULL       NULL
NULL        NOT NULL   LastName
NOT NULL    NULL       FirstName
NOT NULL    NOT NULL   LastName, FirstName

Here is a SQL statement that will do this:

SELECT CASE 
WHEN (LastName IS NULL and FirstName IS NULL) THEN NULL
WHEN (LastName IS NULL and FirstName IS NOT NULL) THEN FirstName
WHEN (LastName IS NOT NULL and FirstName IS NULL) THEN LastName
ELSE LastName + ', ' + FirstName
END AS FirstNameLastName
FROM names_table

This takes advantage of the CASE statement's syntax. It may seem a bit awkward at first, but if you lay it out correctly, it's not all that bad.
--
Timothy Chen Allen
[url]http://www.timallen.org[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources