Separate First/Last names from Field?

[newportri]

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

[-__cfSearching__-]

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.

[Dan_Bracuk]

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

[newportri]

Dan:

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

newportri

[Newsgroup_User]

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

How well do you know your data? Are you sure all your names are just
two 'words'? Not all last names are a single word.

Meet my data killing friend - Mary Sue Von Der Longname.

[newportri]

I can simply go into the table and make sure all entries are 2 words only.

[Dan_Bracuk]

quote:

---

Originally posted by: newportri
I can simply go into the table and make sure all entries are 2 words only.

---

This is a very bad idea.

[-__cfSearching__-]

Ian Skinner wrote:
> Meet my data killing friend - Mary Sue Von Der Longname.

ROFL. Good example.

[newportri]

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...

?

[Newsgroup_User]

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,' '))

[newportri]

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.

[Newsgroup_User]

newportri wrote:
>
> Access doesn't have a "find" function- any idea what the Access version would
> like?
>
> Thanks again for yours and everyone's help.
>

Nope, but I'll bet you the Access documentation would be happy to tell you!

[-__cfSearching__-]

As mentioned above, use your database's string functions.