CODEWITHSUNDEEP
sql-server
Joe Valenti
Joe Valenti

Reputation: 21

SQL SERVER - Need help. I understand you can do isnull , but is there is "notNull?

set DisplayName = concat(Title, ' ', FirstName, ' ', substring(MiddleName), 0, 2), '. ', LastName, ' ', Suffix)

So, there are names with out a suffix (Title, and names with out the middle name, so no middle initial. I want it so when it sets Display name, if it has a Tittle and a middle name than provide the space and the period for the middle name. Right now its included null as a blank space, so right now I will get an extra blank space if there is no title,and a period/space if there is no middle initial; I only want it when the values for Middlename and Title are NOT null

Upvotes: 0

Views: 70

Answers (3)

Fabio
Fabio

Reputation: 32445

If you create DisplayName only for user interface, then I think you can do it in your buisness layer - not sql query

Put all results of sql query in the array or list. Then concatenate only not empty string with empty space between them
For example C# code approach which can be used in others .NET Framework languages

string[] names = {Title, FirstName, MiddleName, LastName, Suffix}
string DisplayName = string.Join(" ", names.Where((value) => String.IsNullOrWhiteSpace(value) == false);

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This is a standard trick:

SELECT  CONCAT(ISNULL('Title' + ' ', ''), 'FirstName', ' ', ISNULL(SUBSTRING('MiddleName', 1, 2) + '. ', ''), 'LastName', ' ', 'Suffix')
SELECT  CONCAT(ISNULL(NULL + ' ', ''), 'FirstName', ' ', ISNULL(NULL + '. ', ''), 'LastName', ' ', 'Suffix')

Outputs:

Title FirstName Mi. LastName Suffix
FirstName LastName Suffix

To apply to your example:

SET DisplayName = CONCAT(ISNULL(Title + ' ', ''), 
                         FirstName, 
                         ' ', 
                         ISNULL(SUBSTRING(MiddleName, 0, 2) + '. ', ''),
                         LastName, 
                         ' ', 
                         Suffix)

Upvotes: 3

Deep Kalra
Deep Kalra

Reputation: 1428

SET displayname = concat(title+' '
, firstname, ' '
, substring(middlename, 0, 2) + '. '
, lastname
, ' ' + suffix)

So what we are doing here is we are using a '+' operator instead of CONCAT. The difference is if a NULL is concatenated using '+' operator it will return a NULL as a result. So if a space will be concatenated with a NULL result, say middle name, the whole thing will become NULL and no blank space will be shown.

Upvotes: 1

Related Questions