1

I have two columns of adresses that I need to join. Column 1 has the adresses formatted as "Some Street 12 A" while column 2 has them formatted as "Some Street 12A."

Ie. the last whitespace between 12 and A is removed in one of the columns. Is it possible to write an SQL condition that recognises the white space following a number when doing a database join?

I can't count white spaces since some street names have white space in them and some don't.

If you have a software specific solution, I have access to QGIS and ArcGIS Desktop (most basic license, whatever it's named at the moment.) If you have a solution in either of these software packages even if it doesn't use SQL, that's fine as well.

2

In QGIS you could try to sanitize your column with spaced characters by using regexp_replace( 'Some street 12 A', '\\s([0-9]+)\\s\w', ' \\1') via Field calculator. You will have to replace 'Some street 12 A' with your column:

enter image description here

it should be working. Hope this helps.

2

In SQL, assuming your database supports regular expressions, you could do something like the following (postGres example). Something similar in oracle should also work. T-SQL is a bit more tricky as it doesn't have regular expressions.

UPDATE someTable
SET someCol = regexp_replace(someCol,'(\m[0-9]+)\M[ ]+\m([a-z]+)\M','\1\2','gi')
WHERE someCol ~* '\m[0-9]+\M[ ]+\m[a-z]+\M'

A quick explanation of the regular expression

\m                               Beginning of word
  ([0-9]+)                       1 or more numbers, saved
          \M                     End of word
            [ ]+                 1 or more spaces
                \m               Beginning of word
                  ([a-z]+)       1 or more letters, saved
                          \M     End of word

The replacement expression is the first and second saved expressions and the 'gi' indicates global, case insensitive replacement.

So this expression will only remove spaces between complete integers and letter only words.

12 A -> 12A
1A B -> 1A B
A1 B -> A1 B
12 3 -> 12 3
12 A3 -> 12 A3
1

Does every address end in the form '12 A' or '12A'? If so, you could split on whitespace (the default, in python anyway), then test the last element to see whether it contains digits or not. Combine the last two elements if the last element is a plain alpha character. This could also work testing the last element for length, provided the last element of an address that's a letter is only one character and the previous condition (all '12 A' or '12A') is met.

# In ArcGIS Field Calculator
# split address field
addresslist = address.split()

# check if last element is an alphabetic character(s) only - no numbers
if addresslist[-1:].isalpha():
    # build new address here from address list per your requirements
    # this should get you started. Post back if you need help with this part.
1

You could try with a replace in Python, removing all [0...9] followed with a space.

def RemoveWhiteAfterNumber(addressField):
     a = addressField
     for i in range(10):
         a = a.replace( str(i) +" ", str(i))
     return a




RemoveWhiteAfterNumber(!addressField!)
  • What is the purpose of a=b, since you're then returning b? It might also be a bit confusing to have the function named Replace with an upper case R, since the lower case replace is built in. Not wrong, just confusing. – recurvata Dec 1 '14 at 14:59
  • 1
    I used the capital letter to avoid confusion with the built in function, but you are right, I should have used a completely different name. As for a=b, if you don't do this in the loop, the modified b will be overwritten by a.replace( str(9)+" ", str(9) ). But again, it is not very nice. I've updated my answer according to your remarks. Thanks. – radouxju Dec 1 '14 at 15:09
0

In most real-world datasets, the specific variation you describe is just the tip of an iceberg. You will typically uncover all manner of variations that will eventually defeat any exact match or scripted code/regex solution. Therefore I would use a Fuzzy Lookup algorithmn for this requirement.

If the data volume is small and the task is a "one off", I would use the Fuzzy Lookup Add-In for Excel:

http://www.microsoft.com/en-au/download/details.aspx?id=15011

If you need a more scalable and/or automated solution, I would use the Fuzzy Lookup Transformation in SQL Server Integration Services:

http://msdn.microsoft.com/en-us/library/ms137786.aspx

Both components can give you either the "best" approximate match or a list of the top candidates, each with similarity and confidence scores.

The "art" is in filtering, sorting & reviewing the results. I usually try to set a minimum confidence & similarity thresholds and automatically accept the best match with a score above those thresholds. Inevitably there are some borderline cases that need manual review.

-1

I pull the spaces out of a field for atlas generation to stop GDAL having a heart-attack when it sees them during a batch conversion.

Instead of MAP_NAME I just use replace(MAP_NAME,' ','_')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.