chuang4630
asked on
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?
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?
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
SELECT *
FROM YourTable
WHERE LEFT(Col1, 1) = '0' OR LEFT(Col2, 1) = '0' OR etc...
Greg
Something like this:
SUBSTRING(YourAddressColum
SUBSTRING(YourAddressColum
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT *
FROM YourTable
WHERE LEFT(Col1, 1) = 0 OR LEFT(Col2, 1) = 0 OR etc...
Greg