Skip to main content
A
Anonymous
August 19, 2008
Question

Separate First/Last names from Field?

  • August 19, 2008
  • 6 replies
  • 1114 views
Greetings

I have a table in which the first and last names are in one field (?) which I need to get into their own fields.

I would create a new table:

CREATE TABLE new_table
AS
SELECT #SpanExcluding(?, ?)# as last_name
#SpanExcluding(?, ?)# as first_name
FROM existing_table; ?

Any help would be appreciated.

newportri
This topic has been closed for replies.

6 replies

-
-__cfSearching__-
Inspiring
August 19, 2008
As mentioned above, use your database's string functions.
N
Newsgroup_User
Inspiring
August 19, 2008
You would use the string manipulation tools available to you in your
database management system of choice to split the string of one field
into to values to update the two new fields you create.

This answer is a bit vague, because all the different database
management systems out there offer slightly different string
manipulation functions.

But I believe your SQL is going to look a bit like this. I may be a bit
off here since I don't do this type of data manipulation very often.

UPDATE myTable
SET
firstName = left(fullName,find(fullName,' ')),
lastName = right(fullName,find(fullName,' '))

A
Anonymous
August 20, 2008
Ian:

Thanks for that.

I'm using Access for now.

I've tried:

<cfquery name="updatedata" datasource="#Request.BaseDSN#">

UPDATE lookup_requestor n
INNER JOIN

lookup_customers o ON o.BidID = n.old_bid_ID
SET

n.requestor_lname = right(ContactName,find(ContactName,' ')),
n.requestor_fname = left(ContactName,find(ContactName,' '))

</cfquery>

Error = "Undefined function 'find' in expression."

Access doesn't have a "find" function- any idea what the Access version would like?

Thanks again for yours and everyone's help.
A
Anonymous
August 19, 2008
Guys:

Thanks- so how would I split the last/first out knowing that there are definitely only 2 text stings separated by a space and both are capitalized...

?

-
-__cfSearching__-
Inspiring
August 19, 2008
Ian Skinner wrote:
> Meet my data killing friend - Mary Sue Von Der Longname.

ROFL. Good example.
D
Dan_Bracuk
Inspiring
August 19, 2008
Why would you create a new table? Why not simply add two columns to your current one?

What format are your current names in? Specifically, how would the following records look?
John Paul Jones
John Paul-Jones
Mr Dan Bracuk
A
Anonymous
August 19, 2008
Dan:

Thanks- it's simply John Jones - and it would be fine to create 2 fields out of one in the same table.

newportri
-
-__cfSearching__-
Inspiring
August 19, 2008
Databases have no knowledge of CF functions. The CF code is executed before the sql is sent to your database. Try using your database's string functions instead: left(), right(), substring(), charindex(), etcetera. The function names may vary.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices