Help with WHERE clause - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Help with WHERE clause

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2013-12-18 : 14:52:37

Hi guys,

Let me explain this the best I can. I have an application in which the user must enter a street address in a textbox. They can enter in just the street only, or may add as well the street number or the street direction.

i.e. Davis Hwy
N Davis Hwy
100 N Davis Hwy

These three components correspond to 3 database fields: streetno, streetdir, and street. Is it possible to construct a WHERE clause which can handle this multiple way in which the user can enter the address?

For instance, I have this coded which can handle those cases where the user enters in all parts of the address (streetno, streetdir, and street):

SELECT *
FROM table
WHERE ISNULL(streetno,'') + ' ' + ISNULL(streetdir,'')
+ ' ' + street = 100 N David Hwy'

Your help is greatly appreciated. Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-12-18 : 16:05:52

This is a hard problem to solve - it always is, when a user is allowed to enter free-form text.

One solution you might consider is to provide an (instant) dropdown list of possible values. Sort of like what you see when you type a search term into google. When the user types say, the first four characters, you would pick up those characters and run a query against the database, something like this and display the results int the dropdown list:

SELECT 
   ISNULL(streetno,'') + ' ' + ISNULL(streetdir,'') + ' ' + street
FROM
   YourTable

Then the user would select from one of the items in the dropdown list (or continue to type, in which case you will repeat the query).

The main issue with this is performance, because you will be running queries against the database. You can tune it to make it faster - I have done a similar thing in my environment very successfully. Google is able to do it successfully from remote servers , send the data via the internet and present the data on your computer. So it can be done, but needs tuning and optimization.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 04:36:56

We usually deal with these things by using Data Cleansing Tool. We will create a repository for the standardized values and do cleansing of input values against it. You can use SSIS for the same.you can create knowledge recovery project in DQS (Data Quality Services) for the same and use it inside SSIS DQS task.
If below SQL 2012, you can use third party tools like Mellisa Data Components in SSIS for the same.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 05:39:28

Dunno if it fixes the whole problem, but I would start with this:


ISNULL(streetno + ' ', '') + ISNULL(streetdir + ' ', '') + street

to avoid any additional spaces preventing the equality test from matching

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources