[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-09-19
6
Medium Priority
?
314 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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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 2000 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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month8 days, 2 hours left to enroll

607 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