Geek Answers Handbook

What is the shortest TSQL combining username that can contain zeros

3 fields: FirstName, MiddleName, LastName

Any field can be zero, but I do not want extra spaces. The format should be "First Middle Last", "First Last", "Last", etc.

+4
source share
10 answers

use UDF:

`Select udfConcatName(First, Middle, Last) from foo`

Thus, all your logic for concatenating names is in one place, and as soon as you wrote it shortly to make a call.

+4
source 
  LTRIM(RTRIM( LTRIM(RTRIM(ISNULL(FirstName, ''))) + ' ' + LTRIM(RTRIM(ISNULL(MiddleName, ''))) + ' ' + LTRIM(ISNULL(LastName, '')) ))

NOTE. This leaves no capital or leading spaces. That is why it is a little uglier than other solutions.

+10
source

Assuming “extra spaces” means extra spaces inserted during concatenation (this is a reasonable assumption, I think. If you have extra spaces in your data, you should clear it):

 ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName, '')

works because you add a space to the name - which if it is NULL gives NULL - which gives an empty string.

Edit: If you do not consider SET OPTION - this may be a connection option or db:

 SET CONCAT_NULL_YIELDS_NULL OFF LTRIM(FirstName + ' ' + NULLIF(MiddleName + ' ', ' ') + LastName)

a little shorter but a little uglier.

Edit2: Since you accepted the UDF answer - IMO, this is a bit of a trickster - here are some in the same vein:

 SELECT a FROM b

b - view .;) Or. stored procedure

 EXEC c

But, since EXEC is optional:

 c
+8
source 
 LTRIM(RTRIM(ISNULL(FirstName, '') + ' ' + LTRIM(ISNULL(MiddleName, '') + ' ' + ISNULL(LastName, ''))))
+3
source

Why not use the computed column in the table that concat for you using your preferred syntax from the numbers posted here? Then you just query the computed column - very elegant, and if you save the computed column, you can even get a slight increase in performance. Example here

+2
source 
 replace(ltrim(rtrim(isnull(FirstName, '') + ' ' + isnull(MiddleName, '') + ' ' + isnull(LastName, ''))), ' ', ' ')
+1
source

LTrim(RTrim(Replace(IsNull(Firstname + ' ', '') + isNull(MiddleName, '') + IsNull(' ' + LastName, ''), ' ', ' ')))

0
source 
 '"' + ltrim(rtrim(isnull(FirstName,''))) + ' ' + ltrim(rtrim(isnull(MiddleName,''))) + ' ' + ltrim(rtrim(isnull(LastName,''))) + '","' + ltrim(rtrim(isnull(FirstName,''))) + ' ' + ltrim(rtrim(isnull(LastName,''))) + '","' + ltrim(rtrim(isnull(LastName,''))) + '"'

Ect

0
source 
 DECLARE @first varchar(10) = 'First' DECLARE @middle varchar(10) = '' DECLARE @last varchar(10) = 'Last' LTRIM(RTRIM( @first + ISNULL(NULLIF(' '+LTRIM(RTRIM(@middle)),' '),'') + ISNULL(NULLIF(' '+LTRIM(RTRIM(@last)),' '),'') ))

WHY IT WORKS

Fields are reduced to an empty string if NULL or spaces using the LTRIM, RTRIM, and ISNULL functions.

 LTRIM(RTRIM(ISNULL(@middle,''))) -- Result is a trimmed non-null string value.

This value is prefixed with a single space, and then compared to a single space using the NULLIF function. If it is equal, the result is NULL. If not equal, then the value is used.

 NULLIF(' '+'',' ') -- this would return NULL NULLIF(' '+'Smith',' ') -- this would return ' Smith'

Finally, ISNULL () is used to convert the NULL passed to NULLIF to an empty string.

 ISNULL(NULL,'') -- this would return '' ISNULL(' Smith','') -- this would return ' Smith'
0
source 
 Select firstname, middlename, lastname, ProvidedName = RTrim(Coalesce(FirstName + ' ','') + Coalesce(MiddleName + ' ', '') + Coalesce(LastName + ' ', '') + COALESCE('' + ' ', '') + COALESCE(NULL, '')) From names
0
source

More articles:


All Articles