query help

I have a name column that contains both first and last
names:
Col1
John Doe
I'd like to split it into two columns, a first and
lastname:
firstname lastname
-- --
John Doe
Anyone have any easy way to do this?
Do you *always* have two words, separated by a space? I.e., what does your data look like?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
> I have a name column that contains both first and last
> names:
> Col1
> --
> John Doe
> I'd like to split it into two columns, a first and
> lastname:
> firstname lastname
> -- --
> John Doe
> Anyone have any easy way to do this?
|||For the most part. There are some names that contain a
middle inital..
the data looks like this:
John Doe
Jane Doe
George W Bush
Bill Clinton
John Kerry
Jim Bob Smith
etc....
I'm not overly concerned with getting everything perfect.
To be honest, I'd be cool with just the first names.
>--Original Message--
>Do you *always* have two words, separated by a space?
I.e., what does your data look like?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
>
>.
>
|||This should get you started:
CREATE TABLE Presidents (
FullName varchar(50)
)
GO
INSERT INTO Frog VALUES ('George W Bush')
INSERT INTO Frog VALUES ('Bill Clinton')
INSERT INTO Frog VALUES ('Ronald Reagan')
INSERT INTO Frog VALUES ('George H Bush')
INSERT INTO Frog VALUES ('Gerald Ford')
INSERT INTO Frog VALUES ('Richard Nixon')
GO
SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
CASE
WHEN PATINDEX('% _ %', FullName) > 0
THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1, 1)
ELSE ''
END AS 'MI',
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last Name'
FROM Presidents
You can look up the various pieces used.
CHARINDEX
PATINDEX
SUBSTRING
REVERSE
CASE
Rick Sawtell
MCT, MCSD, MCDBA
|||Ummm. Change the INSERT INTO commands to reflect the Presidents table...
Sorry bout that.
Rick
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> This should get you started:
> CREATE TABLE Presidents (
> FullName varchar(50)
> )
> GO
> INSERT INTO Frog VALUES ('George W Bush')
> INSERT INTO Frog VALUES ('Bill Clinton')
> INSERT INTO Frog VALUES ('Ronald Reagan')
> INSERT INTO Frog VALUES ('George H Bush')
> INSERT INTO Frog VALUES ('Gerald Ford')
> INSERT INTO Frog VALUES ('Richard Nixon')
> GO
>
> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
> CASE
> WHEN PATINDEX('% _ %', FullName) > 0
> THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1,
1)
> ELSE ''
> END AS 'MI',
> RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last
Name'
> FROM Presidents
>
> You can look up the various pieces used.
> CHARINDEX
> PATINDEX
> SUBSTRING
> REVERSE
> CASE
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||Cool, that did it. One other thing though... Could the
same be used for an address column? I used the same
syntax, but ran into an issue...
The column has a street address:
123 N. Main St.
I used the SQL and pulled the house number, directional,
and suffix, but lost the street name. Any help?
Thanks!

>--Original Message--
>Ummm. Change the INSERT INTO commands to reflect the
Presidents table...[vbcol=seagreen]
>Sorry bout that.
>
>Rick
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
AS 'First Name',[vbcol=seagreen]
(' ', FullName) +1,[vbcol=seagreen]
>1)
(FullName)) - 1) AS 'Last
>Name'
>
>.
>
|||Ummm..
Use the SUBSTRING function to get everything to the right of your
directional. Then apply the same CHARINDEX or PATINDEX functions to the
return value you are looking for from the return value of the SUBSTRING
function.
On a separate note... SQL really isn't the best choice to be doing
procedural language things like this.
If you dumped everything to a text file and used VBScript, you could
probably get this thing hashed out more quickly.
Rick
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:c45a01c47a48$9c9aef50$a301280a@.phx.gbl...[vbcol=seagreen]
> Cool, that did it. One other thing though... Could the
> same be used for an address column? I used the same
> syntax, but ran into an issue...
> The column has a street address:
> 123 N. Main St.
> I used the SQL and pulled the house number, directional,
> and suffix, but lost the street name. Any help?
> Thanks!
> Presidents table...
> AS 'First Name',
> (' ', FullName) +1,
> (FullName)) - 1) AS 'Last