Topic: Concat 3 fields using calculated fields

Hi,
I have 3 fields in my Database:

Title
Firstname
Lastname

I want to merge them and have a single field like this: Title Firstname Lastname

How can I do that using calculated fields?
any query example?

Mnay Thanks

2 (edited by sparrow 2022-04-07 19:30:16)

Re: Concat 3 fields using calculated fields

Hi,
Calc field

(Title || ' ' || Firstname || ' ' || Lastname)  

Re: Concat 3 fields using calculated fields

Hi Both,
Just to add to Sparrow's response, if the 3 fields in your database are mandatory, then you will be okay.  But if any of them are optional, then you need to do an 'ifnull' check otherwise your calculated field will be blank.
Please have a look at the attachment;
1.  Column FULLNAME A shows the problem of optional fields without checking for null.
2.  Column FULLNAME B gets over this problem but if the first field is blank, then the concatenation contains a leading space (and so sorting will be a problem).
3.  Column FULLNAME C trims the leading space so the column will sort correctly.
Regards,
Derek.

Post's attachments

Attachment icon concatenate fields.zip 338.33 kb, 225 downloads since 2022-04-08 

Re: Concat 3 fields using calculated fields

Hi Derek,

Then it's better like this

trim(
ifnull(fname||' ','')||   
ifnull(mname||' ','')||   
ifnull(sname,'')
) 

Extra spaces inside are not displayed as in the case of "Pam_ _ Engels"
We can trim each record separately, but in my opinion it's better to trim before saving to the field base.
This will avoid problems with sorting, output, etc. in the future.

Re: Concat 3 fields using calculated fields

Hi Sparrow,
Agreed, it's the best solution of all the options.
D.

Re: Concat 3 fields using calculated fields

Many Thanks.