Page 1 of 1

[Solved] Concactenate Query Text Fields

PostPosted: Sun Apr 24, 2016 10:20 pm
by PNGBILL
Win10 OO4
Good day, I am new to OOBase and am stuck on a simple Query Issue.
I wish to Create a New Field in a Query FullName. It works but the FirstName and LastName have spaces between them. Is this because they are Text Fields rather than VarChar ?
Code: Select all   Expand viewCollapse view
SELECT "ParticipantID", ( "PartFirstName" + "PartLastName" ) AS "FullName", "PartContNum", "PartMedConcern", "PartEmergNum", "PartEmail", "PartEmergContName" FROM "tblParticipant"

Appreciate any assistance :?

Re: Concactenate Query Text Fields

PostPosted: Sun Apr 24, 2016 11:44 pm
by Sliderule
PNGBILL wrote:Is this because they are Text Fields rather than VarChar ?

Yes.

Since, you have elected to have the database store those fields ( columns ) with a predefined length, and, they are padded with spaces to comprise that length . . . I would recommend you change them to VARCHAR, or, if you are using the Embedded Database ( HSQL Version 1.8.0.10 ) . . . VARCHAR_IGNORECASE ( so, the database back-end will find the characters regardless of CASE UPPER, Mixed, lower ).

Alternatively, you could modify your Query ( not recommended ) as:

Code: Select all   Expand viewCollapse view
SELECT "ParticipantID", ( RTRIM("PartFirstName") || SPACE(1) || RTRIM("PartLastName") ) AS "FullName", "PartContNum", "PartMedConcern", "PartEmergNum", "PartEmail", "PartEmergContName" FROM "tblParticipant"

Explanation:
  1. Use the HSQL function RTRIM, to remove all trailing spaces from the designated columns.
  2. Use the SQL standard || ( two pipe characters withOUT a space between them ) in order to concatenate text strings.
  3. Use the HSQL function SPACE(1) to add one space between the trimmed designated names.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: Concactenate Query Text Fields

PostPosted: Mon Apr 25, 2016 12:03 am
by PNGBILL
Thank you Sliderule. I was thinking VarChar maybe the issue. My past experience in SQL & Access warned to use VarChar as little as possible but I guess times have changed.
This works:
Code: Select all   Expand viewCollapse view
SELECT "ParticipantID", "PartFirstName" || SPACE( 1 ) || "PartLastName" AS "Full Name", "PartContNum", "PartMedConcern", "PartEmergNum", "PartEmail", "PartEmergContName" FROM "tblParticipant"
:D