Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

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

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
chuang4630
Asked:
chuang4630
1 Solution
 
JestersGrindCommented:
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
 
JestersGrindCommented:
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
 
Anthony PerkinsCommented:
Something like this:
SUBSTRING(YourAddressColumn, PATINDEX('%[^0]%', YourAddressColumn), 100)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LowfatspreadCommented:
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
 
chuang4630Author Commented:
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
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now