Topic
  • 1 reply
  • Latest Post - ‏2013-04-22T20:08:46Z by tells
tells
tells
12 Posts

Pinned topic How to match records where first word matches last word

‏2013-04-22T15:43:46Z |

I am trying to write a rule where I need to find where the first word of one field matches the last word of another field.

Here is an example of the data:

System 1 Address Line 1: 316 E 1ST ST

System 2 Address Line 1: E 1 ST 316

To get the first word, I am using this logic. 

substring(adr1,1,index(adr1,' '))

I am not sure what logic would be used to get the last word.

 

 

 

 

  • tells
    tells
    12 Posts

    Re: How to match records where first word matches last word

    ‏2013-04-22T20:08:46Z  

    I kept working on this and found a solution.  I figured out that I don't really need the last word of every field.  I just need to know if the same number of characters from the first word matches the same number of characters at the end of the other field.

    So I used this logic to accomplish that:

    substring(sys1_adr1,1,index(sys1_adr1,' ')) = right(trim(sys2_adr1),len(substring(sys1_adr1,1,index(sys1_adr1,' ')))) 

    I gets the length of the first substring and pulls the same number of characters off the end of the second field.