CODEWITHSUNDEEP
sql-server
Aeonstrife
Aeonstrife

Reputation: 673

Searching for a value within an arbitrary range

I have a database where a user can search for addresses. However, some addresses in the databases are listed in a range. For example, 120-125 main st can be a record in the database. If the user searches for 123 Main St, is there a way to get the 120-125 record to show up? This needs to be rather dynamic to include all ranges so I'm not sure if the BETWEEN clause will work properly. Any ideas?

Upvotes: 0

Views: 62

Answers (2)

Tomalak
Tomalak

Reputation: 338416

Save yourself many, many headaches and make dedicated fields for this kind of data. You might even create a function that parses addresses and fills these fields with the help of a trigger (after insert, update):

create function rangeFrom( @address varchar(100) ) returns int as
begin
  declare @pos int

  set @address = replace(@address, ' ', '') + '.'

  set @pos = patindex('%[0-9]%', @address)
  if @pos > 0
  begin
    set @address = right(@address, len(@address) - @pos + 1)
    set @pos = patindex('%[0-9][^0-9]%', @address)
    return left(@address, @pos)
  end
  return null
end
-- ------------------------------------------------------------
create function rangeTo( @address varchar(100) ) returns int as
begin
  declare @pos int

  set @address = replace(@address, ' ', '') + '.'

  set @pos = patindex('%[0-9]-[0-9]%', @address)
  if @pos > 0
  begin
    set @address = right(@address, len(@address) - @pos - 1)
    set @pos = patindex('%[0-9][^0-9]%', @address)
    return left(@address, @pos)
  end
  return null
end

Later, you can call them (e.g. in your trigger):

select dbo.rangeFrom('120-125 main st')  -- returns 120
select dbo.rangeTo('120-125 main st')    -- returns 125

This way you have actual fields that you can use with the BETWEEN operator.

Upvotes: 2

Alec
Alec

Reputation: 966

You could perhaps use regular expressions to extract values for x and y from "x-y Main Street" from the address line, and then check if your search value is between the two extracted values.

Match on ZIP/postal code to ensure you're looking for records in the same street geographically (this should prevent overlapping ranges also).

Not sure how else you'll be able to do this without having the range defined in separate columns, my solution is not brilliant but hopefully it helps you out if you're forced to use string manipulation.

Upvotes: 0

Related Questions