March 22, 2005 at 11:36 am
Does anyone have a clever way of conditionally concatenating fields?
Example scenario: In a table with contact names and titles I would like to produce a single field that produces one field with a given contact's full name. A full name would consist of the following fields; salutation, firstname, middlename, lastname, and a suffix.
Simply concatenating each of these fields like this: ISNULL(Field1,'') + ' ' ISNULL(Field2,'') + ' ' ISNULL(Field3,'')... will leave an unnecessary blank space when any one of the fields is <null>.
I have looked at using a CASE statement to test each combination of field values with a <null> value, but this is an ugly, ugly "solution." I was hoping a more experienced T-SQL programmer would be able to provide me with a more elegant soution. Thank you all in advance.
%You never know, until you know.%
Todd Capehart
March 22, 2005 at 11:46 am
You could continue concatenating as now, and wrap the completed string in a coutple of calls to REPLACE() that replace any occurrence of 2 spaces with 1 space. The number of times you'd need to call replace() would depend on the maximum number of groups of 2 spaces that could be generated.
Sorry, a bit less ugly than CASE, but not by much.
March 22, 2005 at 11:46 am
Select ISNULL(FirstName + ' ', '') + ISNULL(middlename + ' ', '') + ISNULL(lastname + ' ', '') + ISNULL(suffix, '') as FullName from dbo.Contacts
March 22, 2005 at 11:53 am
[EDITED]
Forgot to mention that must must have CONCAT_NULL_YIELDS_NULL ON for this query to work :
if null = null
print 'RUN CONCAT_NULL_YIELDS_NULL ON'
else
print 'you''re set'
March 22, 2005 at 11:54 am
This will return multiple spaces in the case of NULL fields, as concatenating a space with NULL returns the space.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
March 22, 2005 at 11:55 am
That's the kicker. I got it now. Thanks to all who replied so quickly. I appreciate your input.
Todd Capehart
March 22, 2005 at 12:02 pm
There are way too many options and compatibility settings that change the answer to this. My production DB has to run in 6.5 Compatibility mode because of the vendor application. So, as a result, I can't use many, many SQL 2K features.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
March 22, 2005 at 12:03 pm
Hence my second post :
print 'SET CONCAT_NULL_YIELDS_NULL ON '
SET CONCAT_NULL_YIELDS_NULL ON
Select null + 'A'
print 'SET CONCAT_NULL_YIELDS_NULL OFF '
SET CONCAT_NULL_YIELDS_NULL OFF
Select null + 'A'
SET CONCAT_NULL_YIELDS_NULL ON
----
NULL
(1 ligne(s) affectée(s))
SET CONCAT_NULL_YIELDS_NULL OFF
----
A
(1 ligne(s) affectée(s))
March 22, 2005 at 12:04 pm
Select Ltrim(ISNULL(FirstName, '') + Ltrim(ISNULL(middlename , '') )+ Ltrim(ISNULL(lastname , ''))+ Ltrim(ISNULL(suffix, '')) as FullName from dbo.Contacts
March 22, 2005 at 12:06 pm
Where are you concatenating the spaces between the fields?
Also I think this version would be slower than mine as you call LTrim and ISNULL 4 times/row while I only call ISNULL 4 times/row.
March 22, 2005 at 12:25 pm
You got me! The LTrim does help in cases where only the title or middle name can possibly be null.
March 22, 2005 at 5:51 pm
How about this?
select Ltrim(Isnull(first_name,'') + ' ') +
Ltrim(Isnull(middle_name,'') + ' ') +
Ltrim(Isnull(last_name,'') + ' ') +
Ltrim(Isnull(suffix,'')) as FullName
March 22, 2005 at 9:22 pm
Still the same conclusion, you do 4 trims for nothing when none can be used and give out the same results.
March 23, 2005 at 8:00 am
That may be but I think(?) it works across all option settings and compatibility versions.
March 23, 2005 at 8:58 am
I'll join the fun. In all examples I've seen so far they leave a trailing space if the suffix is NULL. Also, all that I've seen so far will not account for a field being an empty string vs NULL, although the application may prevent this situation. Also, they don't account for the data column containing leading or trailing spaces although the application may prevent that from happening as well. If CONCAT_NULL_YIELDS_NULL is your server's/database's/connection's default setting then relying on it isn't a concern, but if it isn't the default changing it for a specific procedure is a concern. The first post using a couple replaces is the simplest, though it needs an RTRIM around the result as well. If you do need to account for empty string values and leading or trailing space values then a function is going to be your best bet.
ALTER FUNCTION BuildFullName
( @CurrentName varchar(1000)
, @NextName varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
set @NextName = RTRIM( LTRIM( ISNULL( @NextName, '' ) ) )
set @CurrentName = ISNULL( @CurrentName, '' )
if @NextName <> '' set @CurrentName = @CurrentName + case when @CurrentName = '' then '' else ' ' end + @NextName
RETURN @CurrentName
END
SELECT dbo.BuildFullName( dbo.BuildFullName( dbo.BuildFullName( dbo.BuildFullName( dbo.BuildFullName( '', Salutation), FirstName ), MiddleName ), LastName ), NameSuffix ) as FullName
You could make a non recursive version as well. Functions aren't the ideal performance wise, but it gets all the work done and done accurately and legibly. Outside of a function I'd go the REPLACE route.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply