Query query!

Expand Post »

Hi everyone,

I'm trying to write a stored procedure select statement which is a little beyond my experience level.

I have a table in the DB called addresses which contains the following fields:

address, VARCHAR
count, INT
(a few more fields)

My stored procedure will take in a unique address in the following format:

####.####.####.####, VARCHAR

What I need to do is compare the passed in value against the [addresses].[address] values and return the matching row. The problem is I also need to check for for increments on the final four characters of the passed in address, upto the [addresses].[count].

Here is an example, the address table contains:

address = 100.100.2225.100
count = 5
(a few more fields)

The stored procedure value passed in is 100.100.2225.103. In this case thie row should be returned as the passed in address 100.100.2225.103 <= 100.100.2225.(100+5)

I hope this makes sense to someone and is simple to do. This isn't for any specific project or work and is purely for my own personal geekery. My only other option is to do this in code.

Thanks in advance for any pointers, code, links or abuse.

Similar Threads

sql query problem with MS Access and C# in C#
SQL Query Problem in Oracle
Query of Query - Using Count in ColdFusion
i want to save a query in MS Access Database throug c# coding in C#
MySQL++ query question in C++

bugmenot

Reputation Points: 53

Solved Threads: 33

Posting Whiz in Training

Offline

224 posts
since Nov 2006

Permalink

Oct 11th, 2008

Re: Query query!

Try code below:

MS SQL Syntax (Toggle Plain Text)
declare @Ask varchar(255)
 
SELECT @Ask = '100.100.2225.104'
 
CREATE TABLE #tmpAddr (
  Addr varchar(255),
  Inc int)
 
INSERT #tmpAddr select '100.100.2225.100', 5
INSERT #tmpAddr select '100.100.2225.103', 5
INSERT #tmpAddr select '100.100.2225.110', 5
 
SELECT	LEFT(Addr, len(Addr) - charindex('.', reverse(Addr))),
	RIGHT(Addr, charindex('.', reverse(Addr)) - 1)
  FROM #tmpAddr
  WHERE @Ask BETWEEN Addr AND LEFT(Addr, len(Addr) - charindex('.', reverse(Addr))) + '.' + cast(cast(RIGHT(Addr, charindex('.', reverse(Addr)) - 1) AS int) + Inc AS varchar)
 
DROP TABLE #tmpAddr
declare @Ask varchar(255)

select @Ask = '100.100.2225.104'

create table #tmpAddr (
  Addr varchar(255),
  Inc int)

insert #tmpAddr select '100.100.2225.100', 5
insert #tmpAddr select '100.100.2225.103', 5
insert #tmpAddr select '100.100.2225.110', 5

select	left(Addr, len(Addr) - charindex('.', reverse(Addr))),
	right(Addr, charindex('.', reverse(Addr)) - 1)
  from #tmpAddr
  where @Ask between Addr and left(Addr, len(Addr) - charindex('.', reverse(Addr))) + '.' + cast(cast(right(Addr, charindex('.', reverse(Addr)) - 1) as int) + Inc as varchar)

drop table #tmpAddr

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: MSSQL charindex HELP !

Next Thread in MS SQL Forum Timeline: Codings

DaniWeb Message

Cancel Changes

User Name
Password