Conditional Concat with space

SQL++
1

Is there a way to conditional CONCAT in the result. Lets say i have a First ,Middle and Last Name if i use
First || " " || Middle || " " || Last as Name i will have an extra " " space if there is no Middle Name. So i hope there is a way to go specify to only insert space if there is a value

2

https://docs.couchbase.com/server/5.5/n1ql/n1ql-language-reference/condfununknown.html

You can use the conditional functions to test the content of the ‘Middle’ field before the concat.

3

Yes but how can i change the output based on the result of thest. Docs are a bit unclear as it seems test if present or null and that’s it.

select id, first_name || " " || IFMISSINGORNULL(middle_name,  middle_name) || " " || last_name as Name, emails from Contacts
order by createdDateTime DESC
Limit 10

What i am trying to achieve is only add a space and the value of the middle_name value if the value is present and not null. So the desired output should be like this

Tom Miller if no Middle Name’
Tom F Miller if there is the Middle name / Initial

4

select id,
(CASE WHEN (LENGTH(middle_name) > 0) THEN first_name || " " || middle_name|| " " || last_name
ELSE first_name || " " || last_name END) as Name, emails from Contacts
order by createdDateTime DESC
Limit 10

5

Thanks that did the trick and also works if there is no middle_name or value is null in doc.

6

You can also try this.

SELECT  id, first_name || " " || IFMISSINGORNULL(middle_name || " ",  "")  || last_name as Name, emails
FROM Contacts
ORDER BY createdDateTime DESC
LIMIT 10;

SELECT  id, CONCAT(first_name , " ", IFMISSINGORNULL(middle_name || " ",  ""), last_name) as Name, emails
FROM Contacts
ORDER BY createdDateTime DESC
LIMIT 10;
7

Thanks that did the trick,