Where clause for names from A-G? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Where clause for names from A-G?

Author

Topic

Conjurer
Starting Member

30 Posts

Posted - 2006-02-06 : 19:31:35

Anyone done this? I want to add a selection criteria to a report that would let you input an alpha range parameter for the list you want to produce.

So for example, I want to pull customer balances where the customer name starts with a letter between A and G.

I know how to add the criteria box, I am just not sure how to pick the names with my Where clause.

Basically I want a where clause like:

Where first letter of customername >= A AND
first letter of customername <= G

But I am not sure how to code that in SQL.

Do I need a function to find the first letter of the name so I can compare to the input range?

Kristen
Test

22859 Posts

Posted - 2006-02-06 : 19:53:14

[code]
WHERE customername >= 'A'
AND customername < 'H' -- Include the G's, inclusive
[/code]
or if you don't like going up to "H"

you might be happy with:
[code]
WHERE customername >= 'A'
AND customername <= 'GZZZZ'
[/code]
although that could obviously fail with foreign collations.

You could compare the first character of the Customer Name, as you said, but unfortunately this solution won't [AFAIK] use any Index on the Customer Name column, and therefore be less efficient
[code]
WHERE LEFT(customername, 1) >= 'A'
AND LEFT(customername, 1) <= 'G'
[/code]
Kristen

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-07 : 02:44:11

Something like this?


USE Northwind
GO
CREATE PROCEDURE dbo.SelectCustRange (@start CHAR, @end CHAR)
AS
 SELECT CustomerID, CompanyName
  FROM Customers
 WHERE CompanyName BETWEEN @start+'%' AND @end+'%'
GO
EXEC dbo.SelectCustRange 'A', 'G'
DROP PROCEDURE dbo.SelectCustRange

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-07 : 03:53:42

where customername like '[A-G]%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-07 : 03:57:53

quote:
Originally posted by nr

where customername like '[A-G]%'

~~How would you parameterise this?~~

Ignore this! I obviously need coffee on this lousy morning in Germany.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources