Solved

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

Posted on 2011-09-19
6
263 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
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now