Inner joining and removing characters.

• Alex Chavez

  SSChasing Mays

  Points: 621

  More actions

  December 11, 2009 at 4:21 pm

  #137633

  Hello,

  I'm trying to compare 1 table to another that contains extra info that I need. IE Table 1 has Name, DOB, Address and the other table contains billing info and terminiaton dates. I've inner joined on Name and DOB and found tons of matches, but I'm still unable to match some so I've decided to match on Address+Name. I wrote this query here

  DROP TABLE #ABC_MCR_5

  SELECT DISTINCT IADIS..ISMS_REG_B_PAT.MRN, IADIS..ISMS_REG_B_PAT.id, Last_Name, First_Name, address, City, State, ABC_MCR.Zip, Phone, ABC_MCR.[DOB], Mtg_Date, [Mtg_Name]

  INTO #ABC_MCR_5 FROM ABC_MCR

  INNER JOIN IADIS..ISMS_REG_B_PAT ON

  ABC_MCR.Last_Name = IADIS..ISMS_REG_B_PAT.LNM

  AND LEFT(First_NAME,2)=LEFT(FNM,2)

  AND LEFT(address,4) =LEFT(STREET_addr_l1,4)

  Though it still isn't working because some of the addresses in both tables have periods in random places. Such as '1111 E. Valley' or '1111 E Valley Ave.' and so they don't match up. I tried removing all the '.' from one list and comparing, but i still run into the same problem since the two don't match.

  So basically, is there a way to strip out the '.' within my join query?

  Thanks in advance!

• Pavel Pawlowski

  Ten Centuries

  Points: 1244

  More actions

  December 12, 2009 at 4:32 am

  #1091506

  To remove any character or string from within another you can use the REPLACE function

  REPLACE(STREET_addr_l1, '.', '')

  so you will replace the dot by en ampty string and thus removing the dot.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  December 12, 2009 at 12:25 pm

  #1091573

  Here is a method to "adjust" all the addresses to match by removing any unwanted periods .

  CREATE TABLE #Table(Id INT, Address VARCHAR(20),Description VARCHAR(40))

  INSERT INTO #Table

  SELECT 1,'1111 E.Valley St','Period w/o following blank' UNION ALL

  SELECT 2,'1111 E Valley St','No period one blank' UNION ALL

  SELECT 3,'1111 Roosevelt Ave','Just to be different' UNION ALL

  SELECT 4,'1111 E. Valley St','Period with following blank' UNION ALL

  SELECT 5,'1111 E Valley St.','Period at end' UNION ALL

  SELECT 6,'1111 E. Valley St.','2 periods'

  --Test to verify the REPLACE function works as needed

  SELECT Id, Address, REPLACE(REPLACE(address, '.', ' '),' ', ' ') AS 'Modified address' ,Description FROM #Table

  --Clean up

  DROP TABLE #Table

  Results:

  IdAddress Modified address Description

  11111 E.Valley St1111 E Valley StPeriod w/o following blank

  21111 E Valley St1111 E Valley StNo period one blank

  31111 Roosevelt Ave1111 Roosevelt Ave

  41111 E. Valley St1111 E Valley StPeriod with following blank

  51111 E Valley St.1111 E Valley St Period at end

  61111 E. Valley St.1111 E Valley St 2 periods

  Now how to incorporate that clean up T-SQL into your procedure I leave to you. However I would suggest cleaning up the address data in whatever table contains it, and then look critically at your input method and take care of cleaning up unwanted characters BEFORE it is input to the database.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]