3 fields: FirstName, MiddleName, LastName
Any field can be null, but I don't want extra spaces. Format should be "First Middle Last", "First Last", "Last", etc.
views:
2594answers:
7
+4
A:
LTRIM(RTRIM(ISNULL(FirstName, '') + ' ' + LTRIM(ISNULL(MiddleName, '') + ' ' +
ISNULL(LastName, ''))))
devio
2008-12-08 23:47:54
Note that this will leave extra spaces between the middle name and the last name (if the middle name has spaces on the end), and also after the last name (if it has spaces on the end).
Andrew Rollings
2008-12-09 02:05:22
+5
A:
LTRIM(RTRIM(
LTRIM(RTRIM(ISNULL(FirstName, ''))) + ' ' +
LTRIM(RTRIM(ISNULL(MiddleName, ''))) + ' ' +
LTRIM(ISNULL(LastName, ''))
))
NOTE: This won't leave trailing or leading spaces. That's why it's a little bit uglier than other solutions.
Andrew Rollings
2008-12-08 23:48:36
I'd be curious to know (seriously!) why this is unhelpful... Perhaps the downvoter can enlighten me? It actually works exactly as requested and covers all corner cases.
Andrew Rollings
2008-12-09 00:08:51
Andrew, I really hate when this happens. Please vote on uservoice for a change: http://stackoverflow.uservoice.com/pages/general/suggestions/41056
Sunny
2008-12-09 00:42:54
Yeah. Considering the other two solutions don't actually do what is requested... Ah well, guess it's just sour grapes on my part. I would like to see some accountability though :(
Andrew Rollings
2008-12-09 02:06:22
Doesn't it leave a double space in the middle when there's no MiddleName? Probably needs a space-reduction substitution around the whole thing. Or something.
Mike Woodhouse
2008-12-09 09:03:20
Hi Andrew,Interestingly, yours wasn't the answer I down-voted! An earlier revision of Charles Bretana's answer was the one I downvoted (he used TRIM, which isn't a recognised TSQL function - see my comments to his post!). A SO bug perhaps? Anyway, I've removed the incorrect downvote on your answer.
Scott Ferguson
2009-02-02 03:16:47
A:
LTrim(RTrim(Replace(IsNull(Firstname + ' ', '') + isNull(MiddleName, '') + IsNull(' ' + LastName, ''), ' ', ' ')))
Charles Bretana
2008-12-08 23:48:44
+3
A:
use a UDF:
`Select udfConcatName(First, Middle, Last) from foo`
That way all your logic for concatenating names is in one place and once you've gotten it written it's short to call.
Jamal Hansen
2008-12-09 02:16:24
Unless you make it an "inline" udf, this will adversely affect performance, as udfs (except the inline kind) get recompiled on evcery execution. So they're fine for one time use, in a single row select. or to populate a variable b4 a multiple row select, but not on each row of a multiRow select
Charles Bretana
2008-12-09 14:09:23
Good grief. Agreed. This will be as slow as molasses if you have to do it with any great number of values.
Andrew Rollings
2009-02-02 04:17:03
+4
A:
Assuming by "extra spaces", you mean extra spaces inserted during the concatenation (which is a reasonable assumption, I think. If you have extra spaces in your data, you should clean it up):
ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName, '')
works, since you'll add a space to the name - which if it's NULL yields NULL - which yields empty string.
Edit: If you don't count the SET OPTION - which can be a connection or db option:
SET CONCAT_NULL_YIELDS_NULL OFF
LTRIM(FirstName + ' ' + NULLIF(MiddleName + ' ', ' ') + LastName)
is a tiny bit shorter, but a large bit uglier.
Edit2: Since you accepted the UDF answer - IMO, that's a bit of a cheat - here's some in the same vein:
SELECT a FROM b
b is a view. ;) Or. a stored proc,
EXEC c
But, since EXEC is optional:
c
Mark Brackett
2008-12-09 02:18:35
I selected the UDF because is was the shortest practical solution.I would probably use your (pre-edit) solution over a UDF, so I gave you an up vote.
Keith Walton
2008-12-09 03:51:39
A:
'"' + ltrim(rtrim(isnull(FirstName,''))) + ' ' + ltrim(rtrim(isnull(MiddleName,''))) +
' ' + ltrim(rtrim(isnull(LastName,''))) + '","' + ltrim(rtrim(isnull(FirstName,''))) +
' ' + ltrim(rtrim(isnull(LastName,''))) + '","' + ltrim(rtrim(isnull(LastName,''))) +
'"'
ETC
GregD
2008-12-09 02:39:46
+3
A:
Why not use a computed column on the table that performs the concat for you using your preferred syntax from the many posted here? Then you will just query the computed column - very elegant and if you persist the computed column then you may even get slight performance increase. Example here
Rich Andrews
2008-12-09 12:24:31