Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Searching Address by Ranges Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 1:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 7:44 AM
Points: 14, Visits: 23
I have a scenario where I have been given a table full of address ranges that I need to compare against an existing table containing addresses. The address range table is structured with the following columns: StreetName, RangeFrom, RangeTo.

A particular example is that I need to find an address that looks like 1516 Adams St by using the address range table with the following: StreetName = 'Adams', RangeFrom = 1200, RangeTo = 1599. In this case I can use a like statement using '1[2-5][0-9][0-9]' + StreetName + '%' to capture the desired records. However, it becomes a bit more tricky when the ranges are some thing like 1505-1629 or 12-158.

Does anyone know of a good way to perform this function?

Thanks for any assistance you can provide.

Tony Willms
Post #1486408
Posted Tuesday, August 20, 2013 1:25 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:49 AM
Points: 137, Visits: 872
A "good" way to perform your query would be to design the table with address parts instead of address lines.
primary_number:             3127
street_predirection: E
street_name: Warm Springs
street_suffix: Rd
secondary_number: 200
secondary_designator: Ste
city_name: Las Vegas
state_abbreviation: NV
zipcode: 89120
plus4_code: 3134
delivery_point: 50
delivery_point_check_digit: 4
first_line: 3127 E Warm Springs Rd Ste 200
last_line: Las Vegas NV 89120-3134

[url=http://stackoverflow.com/questions/11160192/how-to-parse-freeform-street-postal-address-out-of-text-and-into-components][/url]
Post #1486412
Posted Tuesday, August 20, 2013 1:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 7:44 AM
Points: 14, Visits: 23
Yes, of course that would be ideal. However, I am dealing with data provided by external sources where the data is not split as you indicate above. Therefore, I am hoping to find a way to achieve the desired goal without having to parse everything out first. This may be a one-off query to begin with, so I am hoping not not have to parse out thousands of addresses...
Post #1486414
Posted Tuesday, August 20, 2013 1:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 7:44 AM
Points: 14, Visits: 23
Bill,

I just noticed the url you posted at the bottom of your post. Let me look into that further...
Post #1486416
Posted Tuesday, August 20, 2013 2:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:49 AM
Points: 137, Visits: 872
This function will return an int from the beginning of a string.
SELECT dbo.LeadingNumbers('4568 Lancaster Avenue');

SELECT *
FROM Addresses
WHERE AddressLine like '%Lancaster%'
AND dbo.LeadingNumbers(AddressLine) >= 4000
AND dbo.LeadingNumbers(AddressLine) < 5000
;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.LeadingNumbers') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.LeadingNumbers
GO

create function dbo.LeadingNumbers
(
@in varchar(100)
)
RETURNS int
AS
BEGIN
declare
@out varchar(10),
@ptr int,
@len int

set @len = LEN(@in)
set @ptr = 1

while @ptr <= @len
begin
if charindex(substring(@in, @ptr, 1),'0123456789') = 0
goto done
set @ptr += 1
end

done:
if @ptr = 0
set @out = ''
else
set @out = left(@in, @ptr - 1)

RETURN cast(@out as int)

END
GO

Post #1486428
Posted Wednesday, August 21, 2013 9:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 7:44 AM
Points: 14, Visits: 23
Bill,

Thanks! This did exactly what I needed. I don't know why I didn't think about using a separate function... Sometimes I try to do too much within a stored proc...

Tony
Post #1486779
Posted Wednesday, August 21, 2013 6:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
I have to ask why you'd resort to a UDF (not to mention calling it twice!) when it is quite straightforward to simply strip off the leading number as follows:

WITH Addresses (AddressLine) AS (
SELECT '1505-1629 Adams St'
UNION ALL SELECT '12-158 Adams St'
UNION ALL SELECT '450 Adams St'
UNION ALL SELECT 'Adams St'
UNION ALL SELECT '')
SELECT AddressLine
,HouseNo=CASE PATINDEX('%[^0123456789]%', AddressLine) WHEN 0 THEN ''
ELSE LEFT(AddressLine, PATINDEX('%[^0123456789]%', AddressLine)-1) END
FROM Addresses;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1487016
Posted Thursday, August 22, 2013 4:04 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:49 AM
Points: 137, Visits: 872
I'm completely sick of anti-rbar preachers. They're just getting their ego kicks through trying to one-up everybody. Here is what is happening: code is being run on a VonNeumann architecture. Everything is procedurally processed in loops. Yes, even CTEs are loops - they're just running looping code in a compiled format instead of an interpreted format.

Advantages of CTEs:
1) Run faster
2) Programmers get to display their ability to write one
3) Works similar to nature in its fractal and recursive style

Disadvantages of CTEs:
1) Not reusable compared to functions
2) Doesn't simplify the code one is actually working on
3) Must be rewritten as a UDF when more complex logic is added that requires procedural processing

The post originator specified this was a one-off job. Therefore, extra work in optimization is a complete waste. I gave him a general purpose function. I have hundreds of these that I can quickly put to use in importing weirdly formatted customer data. UDFs are very useful from many points-of-view; CTEs are advantageous from one point of view - production code where performance is top priority.


Post #1487149
Posted Thursday, August 22, 2013 5:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
Actually, I've rarely found CTEs to improve performance over similar set-based constructs, e.g., a derived table. All they do is add readability.

In this case, I wasn't even trying for that. The CTE was simply a convenient place to put some sample data.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1487183
Posted Thursday, August 22, 2013 6:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 6,719, Visits: 13,827
Bill, it's not a personal thing, we all make mistakes. The point of folks posting faster and more efficient code samples is that any posted code here in ssc could be randomly picked up and used. Folks often don't read from the beginning of a thread, nor indeed to the end, hence bad code must be flagged as such as soon as possible, preferably in the very next post and offering a properly written alternative, and an explanation. The same applies to advice and statements.
Folks like Dwain go to considerable lengths to set a high standard for forum code and whilst there may occasionally be an element of friendly competition between ourselves, it's very rarely directed at the OP. Why bother? How important can it be to encourage coders to think about the cost of their queries? You only have to lurk around ssc for a day or two to find out that it’s absolutely vital.
Your function is awful isn’t it? I can’t believe you saved it in your toolkit



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1487216
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse