Search Within Alpha Lower and Upper Limit

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

SQL Server 2000 Forums

SQL Server Development (2000)

Search Within Alpha Lower and Upper Limit

Author

Topic

teaslon
Starting Member

1 Post

Posted - 2004-09-09 : 18:03:53

I have two fields in a table called Lower and Upper. These are varchar fields. The purpose is to enable the user to identify the lower bound and upper bound limit of an address. I enter a lower limit of A and an upper limit of Z.

For a given street my lower limit may be A and my upper limit may be Z. If I want to search for a address that is "A 4th Street" and only search on the "street number" in this case "A". How can I do this?

teaslon

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-09 : 23:39:02

Hi teaslon

since yuo're not getting any posts, I'll hazard a guess that everyone else found your question a bit unclear. Actually I'm not really sure what you're asking.

lets say you have some data like

create table range (upperlimit varchar, lowerlimit varchar)
insert into range (upperlimit, lowerlimit) values ('A','B')

create table addresses (address varchar(50))
insert into addresses (address) values ('A 4th street')
insert into addresses (address) values ('A 5th street')
insert into addresses (address) values ('B 6th street')
insert into addresses (address) values ('Z 7th street')

To find records which begin with a particular string:

select address from addresses where address like 'A%'

or for anything in the range A-Z

select address from addresses where address like '[A-Z]%'

or for a range specified in another table....



--actual code
select address 
from addresses
where address like N'[' + (select upperlimit from range) + '-' + (select lowerlimit from range) + ']' + '%'

Let me know if that's not what you want

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources