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 Assistance

Hi,
Assuming the following table structure and sample data:
CREATE TABLE TestTab
(Col1 varchar(30) NOT NULL,
Col2 varchar(30) NOT NULL,
Col3 varchar(50) NOT NULL)
Col1 Col2 Col3
XYZ 54 Test
XYZ 54 Simple
ABC 28 Bogus
How can I return the only one entry for a combination of Col1 and Col2?
I.e., Resultset
Col1 Col2 Col3
XYZ 54 Test
ABC 28 Bogus
It doesn't matter wich record is return i.e., Col3 Test or Simple is fine.
Thanks
JerrySELECT col1, col2, MIN(col3)
FROM Test
GROUP BY col1, col2;|||>> How can I return the only one entry for a combination of Col1 and Col2?
You have to specify which row you want to return
It may not matter to the users, but it matters to the DBMS to consistently
derive the resultset. One option would be to simply use an extrema function
on col3 like:
SELECT col1, col2, MAX( col3 )
FROM tbl
GROUP BY col1, col2 ;
Anith|||Duh! Spaced that one. Thanks guys!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126563751.937497.46640@.g43g2000cwa.googlegroups.com...
> SELECT col1, col2, MIN(col3)
> FROM Test
> GROUP BY col1, col2;
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?
For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
...
and many other questions need to be asked and answered to come up with the
best choices.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>
|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>
|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
..
and many other questions need to be asked and answered to come up with the
best choices.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> For this particular query, an index on (col2, col1) would most likely be
>> faster. But index tuning isn't done one query at a time.
>> What other queries reference table1 and access col1 and col2?
>> You need to design indexes so that all the queries that access the table
>> will perform reasonably well. There may be other choices of indexes that
>> work well for this query, and also work well for many other queries.
>> What percentage of the table have col2 values less than 100?
>> How many rows fit per page?
>> Are you also performing updates to the table?
>> ..
>> and many other questions need to be asked and answered to come up with
>> the best choices.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Select col1 from table1
>> where col2 < 100
>> In this case, if we have a covered index on col1 and col2,
>> would the index on (col1,col2) or (col2,col1) perform better and faster
>> ?
>> I am leaning towards this order : (col2,col1) as col2 is the leading
>> column and col1 data is just stored along with col2 in the leaf page
>> although col1 would be stored in an ordered fashion.
>> Am i correct in my theory ?
>>
>>
>

query and index as follows

Select col1 from table1
where col2 < 100
In this case, if we have a covered index on col1 and col2,
would the index on (col1,col2) or (col2,col1) perform better and faster ?
I am leaning towards this order : (col2,col1) as col2 is the leading column
and col1 data is just stored along with col2 in the leaf page although col1
would be stored in an ordered fashion.
Am i correct in my theory ?For this particular query, an index on (col2, col1) would most likely be
faster. But index tuning isn't done one query at a time.
What other queries reference table1 and access col1 and col2?
You need to design indexes so that all the queries that access the table
will perform reasonably well. There may be other choices of indexes that
work well for this query, and also work well for many other queries.
What percentage of the table have col2 values less than 100?
How many rows fit per page?
Are you also performing updates to the table?
..
and many other questions need to be asked and answered to come up with the
best choices.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Select col1 from table1
> where col2 < 100
> In this case, if we have a covered index on col1 and col2,
> would the index on (col1,col2) or (col2,col1) perform better and faster ?
> I am leaning towards this order : (col2,col1) as col2 is the leading
> column and col1 data is just stored along with col2 in the leaf page
> although col1 would be stored in an ordered fashion.
> Am i correct in my theory ?
>
>|||Kalen, I was just verifying my theory that the order does matter in a way
and just creating a covering index on all the columns randomly may not be
ideal,assuming if the query below was the only query run with no
modifications.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
> For this particular query, an index on (col2, col1) would most likely be
> faster. But index tuning isn't done one query at a time.
> What other queries reference table1 and access col1 and col2?
> You need to design indexes so that all the queries that access the table
> will perform reasonably well. There may be other choices of indexes that
> work well for this query, and also work well for many other queries.
> What percentage of the table have col2 values less than 100?
> How many rows fit per page?
> Are you also performing updates to the table?
> ..
> and many other questions need to be asked and answered to come up with the
> best choices.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:O5M0xm%23MIHA.1184@.TK2MSFTNGP04.phx.gbl...
>|||> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
Hassan,
The Books Online topic "General Index Design Guidelines
"(http://msdn2.microsoft.com/en-us/library/ms191195.aspx) provides the
following guidance in the section "Column Considerations".
"Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (> ), less than (< ), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should be
ordered based on their level of distinctness, that is, from the most
distinct to the least distinct. "
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Hassan" <hassan@.test.com> wrote in message
news:O0phzAFNIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Kalen, I was just verifying my theory that the order does matter in a way
> and just creating a covering index on all the columns randomly may not be
> ideal,assuming if the query below was the only query run with no
> modifications.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCvbgv%23MIHA.4808@.TK2MSFTNGP05.phx.gbl...
>