Is this possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Is this possible?

Author

Topic

Carl~R
Starting Member

5 Posts

Posted - 2005-11-30 : 16:38:39

First I would like to announce that I am new to sql so please go easy on me

I am trying to split data from one column that contains First and Last names combined, into two separate columns in a different table. The SELECT INTO command seems like it would work except I cannot find a way to split data from all fields within one column. The structure is as follows:

Table: CustomerFiles
Column: CustomerName (contains both first and last)

Table: CustomerCreditAccounts
Column: FirstName
Column: LastName

Any insight into this matter would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-30 : 16:52:38

How is the CustomerName formatted? Is it LastName, FirstName or FirstName <space> LastName?

Tara Kizer
aka tduggan

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-11-30 : 16:58:40

Assuming you have a consistent naming format (see Tara's post), you can do something like this:

Left(CustomerName, Instr(1, " ", CustomerName) - 1) as FirstName, 
Mid(CustomerName, Instr(1, " ", CustomerName) + 1) as LastName

btw - you might want to check the syntax of my Instr's. I can never remember the order of the parameters.....

HTH,

Tim

Carl~R
Starting Member

5 Posts

Posted - 2005-11-30 : 17:06:13

Tara, yes there is a consistent naming format of FirstName <space> LastName.

Tim, I will double-check the syntax and try your suggestion.

Thank you both for helping in this matter.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-30 : 17:55:43

Timmy,

What is INSTR? Do you mean SUBSTRING?

Tara Kizer
aka tduggan

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-11-30 : 18:21:05

Tara,

It's the VBA/Access equivalent of CHARINDEX.

Tim

btw - I thought someone had hijacked your title for a minute until I saw your new sig...... I haven't been 'round here for a while...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-30 : 18:23:13

Oops! I didn't notice this was the Access forum. Sorry!

Tara Kizer
aka tduggan

Carl~R
Starting Member

5 Posts

Posted - 2005-11-30 : 19:02:52

Ok, this is what I have come up with so far...

SELECT CustomerName
INTO CustomerCreditAccounts
FROM CustomerFiles
Left(CustomerName, Instr(CustomerName, " ") - 1) as FirstName,
Mid(CustomerName, Instr(CustomerName, " ") + 1) as LastName

I changed CustomerName inside the INSTR to be the first string based upon MSDN definition of returing the variant string2 is found within string1 = Position at which match is found. Did you mean for the -1 and +1 to be within the INSTR as a compare argument? By the way this statement is getting syntax error so I know it isn't correct yet.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-30 : 19:12:50

I've never used Access, but perhaps this will work:

INSERT INTO CustomerCreditAccounts(FirstName, LastName)
SELECT Left(CustomerName, Instr(CustomerName, " ") - 1) as FirstName,
Mid(CustomerName, Instr(CustomerName, " ") + 1) as LastName
FROM CustomerFiles

Tara Kizer
aka tduggan

Carl~R
Starting Member

5 Posts

Posted - 2005-12-02 : 15:33:16

I feel like we are getting close to the answer here. Thanks for the continued help in this matter.

When I run the above statement posted by Tara, there are no syntax errors but it is 0 rows affected.
I discovered the problem is that there are two other required fields when creating the row, TotalCreditLimit and CreditAccountStatus. Now I cannot figure out how to integrate:

UPDATE CustomerCreditAccounts
SET TotalCreditLimit = 100, CreditAccountStatus = 1;

into the statement of:

INSERT INTO CustomerCreditAccounts (CustomerID, FirstName, LastName, TotalCreditLimit, CreditAccountStatus)
SELECT CustomerID,
LEFT (CustomerName, INSTR (CustomerName, " ") -1) as FirstName,
MID (CustomerName, INSTR (CustomerName, " ") +1) as LastName
FROM CustomerFiles;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-02 : 15:37:57

INSERT INTO CustomerCreditAccounts (CustomerID, FirstName, LastName, TotalCreditLimit, CreditAccountStatus)
SELECT CustomerID,
LEFT (CustomerName, INSTR (CustomerName, " ") -1),
MID (CustomerName, INSTR (CustomerName, " ") +1),
100,
1
FROM CustomerFiles

Tara Kizer
aka tduggan

Carl~R
Starting Member

5 Posts

Posted - 2005-12-02 : 16:50:14

Thanks Tara, that did it! Beautiful!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-03 : 02:38:40

quote:
INSERT INTO CustomerCreditAccounts (CustomerID, FirstName, LastName, TotalCreditLimit, CreditAccountStatus)
SELECT CustomerID,
LEFT (CustomerName, INSTR (CustomerName, " ") -1) as FirstName,
MID (CustomerName, INSTR (CustomerName, " ") +1) as LastName
FROM CustomerFiles;

When you refer five columns in the Insert Statement then the Select Should select five columns

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources