Insert Question

I need to insert a last and first name field taken from a full name
field on the same table. If I have 25 rows of customers in this table,
what would be the best way to do this? I can split the full name into 2
fields with the syntax below, but I need help with the syntax for
inserting these to fields on the existing rows. Help appreciated.
Thanks.

SELECT Left([FullName],InStr(1,[FullName]," ")-1),
Right(Trim([FullName]),Len(Trim([FullName]))-InStr(1,[FullName]," "))

I have fields in my table like:
CustomerNumber (populated)
CustomerName (populated)
FullName (populated)
Fname
Lname
SalesPersonCode (populated)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Frank Py (fpy@.proactnet.com) writes:
> I need to insert a last and first name field taken from a full name
> field on the same table. If I have 25 rows of customers in this table,
> what would be the best way to do this? I can split the full name into 2
> fields with the syntax below, but I need help with the syntax for
> inserting these to fields on the existing rows. Help appreciated.
> Thanks.
> SELECT Left([FullName],InStr(1,[FullName]," ")-1),
> Right(Trim([FullName]),Len(Trim([FullName]))-InStr(1,[FullName]," "))
> I have fields in my table like:
> CustomerNumber (populated)
> CustomerName (populated)
> FullName (populated)
> Fname
> Lname
> SalesPersonCode (populated)

UPDATE tbl
SET Fname = Left([FullName], InStr(1,[FullName]," ")-1),
Lname = Right(Trim([FullName]),Len(Trim([FullName])) -
InStr(1,[FullName]," "))

Not that this will work on SQL Server, since there is no InStr or Trim
functions on SQL Server. But if you are using Access and just don't know
which newsgroup to post to, this should do alright, since this is
standard SQL, save the functions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, I see what you mean. This worked well with Access, but how would
I convert this to something SQL could use? You mentioned saving
functions. Help appreciated. Thanks.

UPDATE tbl
SET Fname = Left([FullName], InStr(1,[FullName]," ")-1),
Lname = Right(Trim([FullName]),Len(Trim([FullName])) -
InStr(1,[FullName]," "))
-------
Result:
'InStr' is not a recognized function name.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Frank Py (fpy@.proactnet.com) writes:
> Thanks, I see what you mean. This worked well with Access, but how would
> I convert this to something SQL could use? You mentioned saving
> functions. Help appreciated. Thanks.
> UPDATE tbl
> SET Fname = Left([FullName], InStr(1,[FullName]," ")-1),
> Lname = Right(Trim([FullName]),Len(Trim([FullName])) -
> InStr(1,[FullName]," "))
> -------
> Result:
> 'InStr' is not a recognized function name.

Look in Books Online, the T-SQL Reference. Find the Functions topic, and
then the string functions topic. I cannot translate the above to T-SQL,
as I don't what the Access functions do.

And since using Books Online is a far quicker way to get answers to
simple questions than asking a newsgroup, I figured I should get you
started.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp