amateur question - 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

General SQL Server Forums

New to SQL Server Programming

amateur question

Author

Topic

tchelcho
Starting Member

4 Posts

Posted - 2009-04-30 : 13:33:29

I'm trying to write a select statement to filter out a listing in the database.
A sample of this would be

Table Name: SVC

TYPE NAME
LAB Acetaminophen
LAB Amylase
LAB BMP
LAB CBC
LAB D-Dimer

etc....
It has many more rows but I would like to write a select statement to just grab the ones that NAME starts with A through G alphabetically.

I was thinking it would go like:
SELECT * from svc where type='LAB' and NAME LIKE 'A%' OR NAME LIKE 'B%'

I am sure there is a much easier way to get this with an IN or an OR type of clause which would encompass that first part of the alphabet rather than me listing each letter at a time like that.

Any suggestions?
Thanks.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 13:39:57

SELECT * FROM [svc] WHERE type = 'LAB' AND SUBSTRING(Name, 0,1) BETWEEN 'A' AND 'G';

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-30 : 13:43:25

quote:
Originally posted by whitefang

SELECT * FROM [svc] WHERE type = 'LAB' AND SUBSTRING(Name, 0,1) BETWEEN 'A' AND 'G';

What about the performance implications of using a function on the Name column? Lack of an index...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 13:46:03

Obviously, it's not an enterprise app. It's probably a simple reporting app and the fact that it probably contains less than a million records, it doesn't matter performance wise.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-30 : 13:48:20

I don't know how you came to that conclusion. I don't see any of that information in this topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 13:52:06

quote:
Originally posted by tchelcho

I'm trying to write a select statement to filter out a listing in the database.
A sample of this would be

Table Name: SVC

TYPE NAME
LAB Acetaminophen
LAB Amylase
LAB BMP
LAB CBC
LAB D-Dimer

etc....
It has many more rows but I would like to write a select statement to just grab the ones that NAME starts with A through G alphabetically.

I was thinking it would go like:
SELECT * from svc where type='LAB' and NAME LIKE 'A%' OR NAME LIKE 'B%'

I am sure there is a much easier way to get this with an IN or an OR type of clause which would encompass that first part of the alphabet rather than me listing each letter at a time like that.

Any suggestions?
Thanks.

maybe this?

SELECT * from svc where type='LAB' and NAME LIKE '[A-G]%' order by NAME

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-04-30 : 13:52:33

Assuming that you are using the default case insensitive collation and the NAME column is data type VARCHAR, this should do what you want.


select
	*
from
	SVC a
where
	a.TYPE =  'LAB'	and
	a.NAME >= 'A'	and
	a.NAME <  'H'

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 13:53:42

Instead of SUBSTRING it would be better to use LIKE:

WHERE name LIKE '[A-G]%'

EDIT: bah, that's what I get for allowing a coworker to inerrupt me.. :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-04-30 : 14:52:25

quote:
Originally posted by whitefang

Obviously, it's not an enterprise app. It's probably a simple reporting app and the fact that it probably contains less than a million records, it doesn't matter performance wise.

Obviously, you just made up assumptions with no evidence to support them.

There is no point in using a worse solution when a better one is just as easy to code.

CODO ERGO SUM

mastajbl
Starting Member

42 Posts

Posted - 2009-04-30 : 16:15:44

I have never heard of a "worse" solution...at times any solution that will produce an end result is nice to have. Fine tuning code and performance can be worked on progressively while the database is functioning for the user/s.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-30 : 16:22:45

quote:
Originally posted by mastajbl

I have never heard of a "worse" solution...at times any solution that will produce an end result is nice to have. Fine tuning code and performance can be worked on progressively while the database is functioning for the user/s.

I find this to be a bit ironic coming from "Quality Management".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

mastajbl
Starting Member

42 Posts

Posted - 2009-04-30 : 16:29:18

I guess I just have a different look on things than most...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-30 : 16:31:27

quote:
I have never heard of a "worse" solution...at times any solution that will produce an end result is nice to have

Do you make 1 right turn, or 3 left turns? Or 7 left turns? Or 11? They all produce the same end result, but one of them is definitely better than the others.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-04-30 : 17:17:39

quote:
Originally posted by mastajbl

I have never heard of a "worse" solution...at times any solution that will produce an end result is nice to have. Fine tuning code and performance can be worked on progressively while the database is functioning for the user/s.

Would you consider a car that only went 5 miles per hour as good as one that went 60 miles per hour if you have a 10 mile commute to work? After all, they will both get you there.

Would you consider a query that ran 60 times as long OK if you are the one setting there waiting for it to complete for 2 minutes instead of 2 seconds?

How about if you are a business owner with 400 call center employees that run the same poor performing query 20 times per day, so that each one spends 40 minutes per day waiting for that query, and consuming the equivilent work hours of 33 full time employees?

Why deploy something worse, when it is just as easy to deploy something better, provided the developer knows what they are doing? Since many people read these posts, we try to provide the best solutions.

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-30 : 17:19:47

My systems would see even working queries to be a failure if they aren't as efficient as possible. Data accuracy is probably the most important and then performance is right behind it.

I've got timeout values set to 300 milliseconds in one of my systems. Inefficient code is not tolerated there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources