Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:

> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave
,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
James
The problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:

> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Adding dates in SQL

I am trying to pull only records that are greater than 1 month prior to today's date. This is what I have so far...

select *
from MyTable
where eventDateStart > '$Now'
order by eventDateStart

$Now is a variable that pulls in today's date. This sql statement delivers only records that have a eventDateStart greater than today. My problem is I do not know how to make it so it only shows records that are 1 month prior.

Any idea how to do this?Have a look at the DATEDIFF function in SQL Server|||This should do it for you:

SELECT * FROM MyTable
WHERE eventDateStart < DATEADD(m, -1, GETDATE())

Cheers

Gary|||Ok, this is frustrating, I can not get the DATEADD function to work for some reason. This is exactly what I want..

select *
from MyTable
where eventDateStart > DATEADD (m,-1,'2004/12/26')

This "SHOULD" return all the events that have a start date greater than November 26,2004 right? Am I crazy or something? If I get rid of the dateadd function and just have this where clause...

where eventDateStart > '2004/12/26'

it returns exactly what it should...all events that have a start date later than the 12/26/2004. But as soon as I try to DATEADD it returns zero rows. What the heck am I doing wrong|||Hi again,

Sorry I screwed up the first time as I should have said:
eventDateStart > DATEADD(m, -1, GETDATE())
instead of
eventDaytStart < DATEADD(m, -1, GETDATE())

In any case I checked your hardcoded version (SELECT DATEADD (m,-1,'2004/12/26') )and my version (SELECT DATEADD(m, -1, GETDATE())) in query analyzer and both give me the expected results.

I don't know if this is typo on your part but your DATEADD version will produce a comparison date of 2004/11/26 not 2004/12/26, so you are likely to get different results. Try it in query analyzer making sure you are using a dateadd that will be the same date as your hardcoded value and see what happens.

If that failds post the entire query/procedure and I'll look at it again because you're right, there is no reason why this should be difficult.

Cheers

Gary