[Solved] Concactenate Query Text Fields

Creating tables and queries

[Solved] Concactenate Query Text Fields

Postby PNGBILL » Sun Apr 24, 2016 10:20 pm

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 :?
Last edited by PNGBILL on Mon Apr 25, 2016 12:05 am, edited 1 time in total.
OpenOffice4-Win10
PNGBILL
 
Posts: 2
Joined: Sun Apr 24, 2016 3:39 am

Re: Concactenate Query Text Fields

Postby Sliderule » Sun Apr 24, 2016 11:44 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1244
Joined: Thu Nov 29, 2007 9:46 am

Re: Concactenate Query Text Fields

Postby PNGBILL » Mon Apr 25, 2016 12:03 am

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
OpenOffice4-Win10
PNGBILL
 
Posts: 2
Joined: Sun Apr 24, 2016 3:39 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests