Ask Your Question

How Do I Separate a Single Column [Name] Into Two Separate Columns [First Name] [Last Name]

asked 2017-03-25 19:04:00 +0200

iPyrrebpsaR gravatar image

I've managed to import my Data into a LibreOffice Base .odb Database Form but the First and Last Names are Combined into One Column and I need them Separated into Two Columns. I've done a lot of research but can't find a tutorial that explains how to separate this data. I'm a senior, learning on a Linux machine and not computer literate so I require simple/basic instructions.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-03-25 20:26:32 +0200

Ratslinger gravatar image

updated 2017-03-25 20:30:05 +0200

Your question lacks certain information. First you have a table with a field containing a full name. Now are you asking to take that name and create in the same table a new field for the first name and a new field for the last name?

What is the construct of the full name field - first name, space, last name? Are there middle initials - sometimes, always? If middle initials what are to be done with those?

If the full name field is first name, space, last name and you simply want to extract the separate parts, this SQL statement will work in a query:

SELECT RTRIM( SUBSTR( "Employees2"."FULLNAME", 1, LOCATE( SPACE( 1 ), "Employees2"."FULLNAME" ) ) ) AS "First Name", SUBSTR( "Employees2"."FULLNAME", LOCATE( SPACE( 1 ), "Employees2"."FULLNAME" ) + 1 ) AS "Last Name" FROM "Employees2"

Here you would substitute your table name for "Employees2" and your field name for "FULLNAME". The results of the query give one column for first name and one column for last name.

If you are looking to actually update the table with new fields, you need to add the two new fields, then create an UPDATE statement based upon the above to extract the portions and insert into proper new fields created.

All this is based upon you using the database (HSQLDB) which comes with Base. If using a different external database, the syntax may change.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools


Asked: 2017-03-25 19:04:00 +0200

Seen: 601 times

Last updated: Mar 25 '17