Solved

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

Posted on 2011-09-19
6
297 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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