AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for version 2 Open Office database tutorials.

Open Office 2 Base (database)
How To Concatenate Strings

You may find that the database being shipped with OpenOffice (ver.2) delights you as much as it has me. This page, and the others linked to it, can help you use it.

Forget any experiences you may have had with Adabas, which came with Star Office, the commercial version of Open Office 1. The Open Office Version 2 database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including Base, is free! But don't let that fool you. It's not new. Big organizations, civil and governmental, are adopting it as their standard office suite... and saving million$.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window.

Page contents © TK Boyd, Sheepdog Software ®, 3/08.



String concatenation: The quick answer_______________

I'll go into more detail in a moment. For now a concise answer.

Anywhere you can put a string, you can put....

CONCAT('Fish','cakes')

First thing to know: There can only be two terms. That's okay, though, 'cause you can "cheat"....

CONCAT('One',CONCAT('Two','Three'))

Of course, you probably want to work with the contents of fields from your records. If your database has fields called FirstName and Lastname, then you can do....

CONCAT("FirstName","LastName")

(That, of course would give rise to things like FredBloggs... no space between the names, so you would actually, in practice, use the messier....

CONCAT("FirstName",CONCAT(' ',"LastName"))

Second thing to notice: When you refer to a field, put the name in quotation marks, "s. When you are specifying a literal, put it in apostrophes: 's.


Good news!_____________

Beginning with ooBase 2.4 there's a new way to concatenate: A double |. (That's two keystrokes, as in XX. The "|" key, on my US keyboard, is a shifted "\", just above the Enter key. I couldn't find it when my keyboard was set (Windows international settings) as a UK keyboard.>

So our earlier, refined FirstName-space-LastName becomes, from ooBase 2.4 onwards....

"FirstName"||' '||"LastName"

(No "CONCAT", no parentheses "()".)


A little more detail_____________

Let's say you have a database with the following fields. For this simple case, they're all text fields. The first is the table's primary key.

.... and you want a list like....

Easy!

(Create the table, give it the data shown.)

Select the Queries part of the ooBase main project management page

Click on "Create Query In Design Mode"

If it isn't there straight away, select your table to be in the panel at the top of the query designer.

To get things started, select FirstName from the drop down list for "Field" in the first column of the design grid in the bottom of the query designer, and "EmployeeID" for the field for the second column.

Save and run the query.

Fiddle with it all until you get a sensible result! (Use the "Run query" button on the toolbar.)

Alter the field spec for the first column to....

CONCAT("LastName",CONCAT(', ',"FirstName"))

Save query, run query. Success! (I hope!)

(To make the column headings nicer, fill something suitable in on the Alias line in the bottom pane of the design window. Call the first column "Name", and things will "work" when you try what comes next.)

Want to do more? Create a report... you can use the Report Wizard... which reports on the query you just made, and sorts the output by Name.

If you are using ooBase 2.4, the following is ALL you need in the field box of the first column:

"FirstName"||' '||"LastName"

Is your life like mine? That page... which boils down to not much more than....

CONCAT("FirstName","LastName")

CONCAT("FirstName",CONCAT(' ',"LastName"))

... and...

"FirstName"||' '||"LastName"

... took TWO DAYS to get out the door. Sigh. You know the feeling!! (Please give a piece of my shareware, or even freeware, a try???)



Editorial Philosophy

This material is "browser-friendly"... in particular, you can (and I recommend that you do!) make your browser window narrow, so that you don't have to try to read long lines.

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. See the main index to this material for more information about the way it is posted.


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.


Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .