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

Query Efficiency

Tbl_Contact
Contact_ID
FirstName
LastName
Tbl_Contact_Detail
Contact_ID
Question_ID
The combination of Contact_ID and Question_ID is unique in
Tbl_Contact_Detail. There are millions of records in this table. Basically
I need to write an optimized query for returning the Contact_ID, FirstName
and LastName of all contacts that have records in Tbl_Contact_Detail where
Question_ID = 44 and a separate record where Question_ID = 45. What's the
fastest approach?
Thanks a tonThe best approach depends on the right indexing on Tbl_Contact_Detail.
It also probably depends on how what percentage of the contacts have
those questions, and how many questions there are per contact.
Here are some things you could try.
If there are no indexes on Tbl_Contact_Detail this might still perform
reasonably. If there is a clustered index on Question_ID it might
also perform well.
SELECT *
FROM Tbl_Contact as A
JOIN (SELECT Contact_ID
FROM Tbl_Contact_Detail
WHERE Question_ID IN (44, 45)
GROUP BY Contact_ID
HAVING COUNT(distinct Question_ID) = 2) as B
ON A.Contact_ID = B.Contact_ID
This query would not work well unless there is an index on
(Contact_ID, Question_ID), or the reverse. If there are very few
questions per Contact_ID it might not perform too badly if there is
just an index on Contact_ID. In this case non-clustered indexes might
work better than clustered, particularly if Tbl_Contact_Detail has
more columns than were shown.
SELECT *
FROM Tbl_Contact as A
WHERE EXISTS
(SELECT *
FROM Tbl_Contact_Detail as B
WHERE A.Contact_ID = B.Contact_ID
AND B.Question_ID = 44)
AND EXISTS
(SELECT *
FROM Tbl_Contact_Detail as B
WHERE A.Contact_ID = B.Contact_ID
AND B.Question_ID = 45)
Of course the thing to do is run some tests and see how they go.
Roy Harvey
Beacon Falls, CT
On Tue, 25 Sep 2007 16:40:30 -0400, "James" <neg@.tory.com> wrote:
>Tbl_Contact
>Contact_ID
>FirstName
>LastName
>Tbl_Contact_Detail
>Contact_ID
>Question_ID
>The combination of Contact_ID and Question_ID is unique in
>Tbl_Contact_Detail. There are millions of records in this table. Basically
>I need to write an optimized query for returning the Contact_ID, FirstName
>and LastName of all contacts that have records in Tbl_Contact_Detail where
>Question_ID = 44 and a separate record where Question_ID = 45. What's the
>fastest approach?
>Thanks a ton
>|||Hi James
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data. You would need to try each different solution to see if it is
using your indexes and of the performance is ok
try:
SELECT C.Contact_ID, C.FirstName, C.LastName
FROM dbo.tbl_contract C
JOIN dbo.tbl_Contact_Detail D1 ON C.Contact_ID = D1.Contact_ID AND
D1.Question_ID = 44
JOIN dbo.tbl_Contact_Detail D2 ON C.Contact_ID = D2.Contact_ID AND
D2.Question_ID = 45
or
SELECT C.Contact_ID, C.FirstName, C.LastName
FROM dbo.tbl_contract C
JOIN ( SELECT Contact_ID
FROM dbo.tbl_Contact_Detail
WHERE Question_ID = 44
OR Question_ID = 45
GROUP BY Contact_ID
HAVING COUNT(*) = 2 ) D ON C.Contact_ID = D.Contact_ID
John
"James" wrote:
> Tbl_Contact
> Contact_ID
> FirstName
> LastName
> Tbl_Contact_Detail
> Contact_ID
> Question_ID
> The combination of Contact_ID and Question_ID is unique in
> Tbl_Contact_Detail. There are millions of records in this table. Basically
> I need to write an optimized query for returning the Contact_ID, FirstName
> and LastName of all contacts that have records in Tbl_Contact_Detail where
> Question_ID = 44 and a separate record where Question_ID = 45. What's the
> fastest approach?
> Thanks a ton
>
>