Field Concatenation with Line Feed / New Line in Base

[PhLo]

Is it possible to have line feeds between fields/columns when concatenating them together? I am hoping to concatenate full mailing addresses together like this…

address1 || line-feed || address2 || line-feed || City || ', ’ || State || ’ ’ || Zip

or a similar result with a CONCAT() so that the result will be something like

12345 Any Street

Somewhere, WI 54911

(but without the blank space between lines… this forum does that to paragraphs)

The primary reason I want this is to keep from having to position several form fields close together to approximate that appearance. I would like it to be all in one continuous “paragraph.” Sometimes there will be an address1 and address2, other times just an address1. Not sure if queries in Base can have conditions like IF to only add address2 if it isn’t blank?

I’ve seen examples on this forum and elsewhere concatenating first and last name with a space or comma between… but what if a person only has a first name in a record? Is there a way to keep there from being a space/comma delimiter using a condition?

In like manner, I’m looking to create a paragraph with line feeds and only have the address2 line feed when it’s not blank so there’s not an empty space there. All this would be easy in a programming language like PHP, Javascript or whatever. But I was hoping there was a way to do some or all of it in the query itself so a macro wouldn’t be necessary.

Possible? I’m kinda guessing no, but can’t hurt to ask.

In case it matters, I am using MySQL as the backend rather than the internal database supplied by Base. Perhaps in that case I could use MySQL’s more advanced querying options with a direct SQL statement?

[PhLo]

I was previously misinformed about having to nest CONCAT functions because I read LO Base & Hsqldb only support two arguments or strings per CONCAT. See Ratslinger’s solution below because he cleared this up. You don’t need to nest them. All the arguments can go in a single function, like this… CONCAT(address1, address2, city, state, zip), etc. Maybe if you have an older version of LibreOffice you could still have the limitation. Upgrade and enjoy!

[Ratslinger]

Hello,

Your embedded CONCAT is over the top. A Case When in the middle of the concat will work such as:

concat(Name, CHAR(10),
     Case When Addr2 IS NULL Then Concat(Addr1,CHAR(10), City)
          Else
               Concat(Addr1,CHAR(10),Addr2,CHAR(10),City) End ) As MyLabel

What is happening here is that you are doing separate concats with different data within the concat depending upon Address #2.

Tested in MySQL query & results on a form.

[PhLo]

Oh thank goodness! Apparently there’s a lot of outdated information still out there. Many resources on the LO Base CONCAT function claim it can only support 2 arguments, thus the NASTY nested CONCAT mess you see I was using. I guess I should have experimented, and I would have found those people’s information was outdated. Apparently an older version of HSQLDB only supported 2 arguments per CONCAT. Since you helped me break free from that curse, you win! Sorry, I’m too new to upvote. Thanks!

[Ratslinger]

@PhLo Actually it has nothing to do with Base. In a query you can bypass the interpreter and the SQL goes straight to the database which governs the syntax. The same results from an SQL statement may require quite a different structure depending upon the database - HSQLDB, MySQL, PostgreSQL, Firebird, etc.

Your best bet is to use the documentation from the database in use.

[PhLo]

Cool. I’m fairly familiar with MySQL’s statements as a web developer, but I didn’t know why Base had two query writing modes: “direct” or not. I figured “direct” would pass the statements verbatim to MySQL instead of reformatting them to Base’s internal syntax? I have not been using “direct” because I supposed Base might work better using its own internal, rewritten statements (?). I never use Base’ visual builder. I typically write/test my queries first in phpMyAdmin & copy them over to Base.

[Ratslinger]

You almost have it. There is no reformatting. With Run SQL command directly off, the interpreter is performing some verification of the SQL syntax as it applies to HSQL v1.8 embedded - no reformatting. This is why you may get an SQL error when there is no real error, especially if the DB is other than the default. Turn on Run SQL command directly and the verification is bypassed.