#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2005
    Posts
    137
    Rep Power
    11

    Joins using trimmed columns


    Hello Everyone -

    I have several different tables that I am looking to join based on what we can call LDate and PolicyNum columns. The issue is that some of our legacy ETL has been inserting PolicyNum with a blank character at the end in one of our tables where the other table I want to join it with does not have the blank character.

    I am really bad at SQL so what I would usually do is just do a RTrim(PolicyNum) into a temp table and then use that as a reference but really don't want to do that. Can anyone offer some sample code to work with the following example query requirements:

    TableA (the one with the extra character at the end of the policy number)

    Columns:
    LDate PolicyNum PolicyAmount

    TableB

    Columns:
    LDate PolicyNum PolicyHolderAge

    Thanks in advance!!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Code:
    SELECT a.LDate 
         , a.PolicyNum 
         , a.PolicyAmount
         , b.PolicyHolderAge
      FROM TableA AS a
    INNER
      JOIN TableB AS b
        ON b.PolicyNum = RTRIM(a.PolicyNum)
       AND b.LDate = a.LDate
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo