Solved

How to query the DB when the data field has leading '0's?

Posted on 2011-09-19
6
306 Views
Last Modified: 2012-05-12
What we need is a query to search for the Address table for a likely match. We have found, for some reason, the data in Address table is wierd. For example, the street name read like this:

0N68 WILLOW RD
0064 Albaney Dr
0 WEST MAIN ST

The comparison requires the strip-off of all leading '0's and letters. The street should start with a number.

How do I struct a query to accomplish that?
0
Comment
Question by:chuang4630
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36562994
You can search for leading zeros like this.

SELECT *
FROM YourTable
WHERE LEFT(Col1, 1) = 0 OR LEFT(Col2, 1) = 0 OR etc...

Greg

0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36563005
Actually the zeros need to be in single quotes.

SELECT *
FROM YourTable
WHERE LEFT(Col1, 1) = '0' OR LEFT(Col2, 1) = '0' OR etc...

Greg

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36564294
Something like this:
SUBSTRING(YourAddressColumn, PATINDEX('%[^0]%', YourAddressColumn), 100)
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36564442
can you be more specific about your requirements?

i don't find it clear what you are trying to achieve...

surely if your are trying to search an address at street level ...
you need to have extracted the "street" name itself... for your search in which case your search would be

where street like '%main%'   ?  

for  123 Main Street   --- since you couldn't have any confidence on the sort of abbreviations used for the suffix (street,St,Str,...)?

similarly 65 copper gardens road ...

you'd want to attempt to look for  '%copper%gardens%'  ?

please provide more detail on the background to the requirement.
0
 
LVL 1

Author Comment

by:chuang4630
ID: 36567177
The requirement is to find the likely match between two sets of address. The first set is from an input file. The second set is in the Address table. Coupling with other factors, such as state, zip, Last-LastName, we hopefully find the the customer we are looking for. Such requirement results from the legacy EDI database and the "free text" style input data source, which we cannot control.

I have already "cleaned up" the input data. We cannot touch the EDI data in the Address table. Here are some example of the street address we have (in both Address table and input data):

000 W Main Street
00100 E.54ST
0456 E54ST
056N W 54ST
341 W. 54th ST. APT.D
345 West 42nd ST
123 W.43ST





0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36568389
So something like (correct the obvious typo in xSELECT):
xSELECT *
FROM Input t1 INNER JOIN Address t2 ON t1.YourAddressColumn = SUBSTRING(t2.YourAddressColumn, PATINDEX('%[^0]%', t2.YourAddressColumn), 100)

Performance is going to be pretty awful, but you have not got much choice there.

Alternatively you could insert all the data in the Address table into a table in corrected format as in:
SELECT *, SUBSTRING(t2.YourAddressColumn, PATINDEX('%[^0]%', t2.YourAddressColumn), 100)
INTO Address_Corrected
FROM Address
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question