Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Answered: Join On Similar Data

    Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like
    employeeID
    R_12345678
    S_5555555
    T_777777
    U_7777

    Our new data structure simply removed the letter & underscore.
    employeeID
    12345678
    5555555
    777777
    7777

    Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?

  2. Best Answer
    Posted by gvee

    "Alternative is to use SubString:
    Code:
    SubString(column_name, 3, 50)
    Where:
    3 = the first character you want to include (i.e. skip the first two)
    50 = the length of the field definition [or higher] (e.g. if column_name is a varchar(937), use 937+)"


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I would create a new view "Old_to_new" this query would tidy up employeeID in Old table (substring i think is what you are look for)

    havent had this hat on for a long time.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #3
    Join Date
    Feb 2012
    Posts
    188
    Use a case statement with substring to account for each different possible employeeid length? Is that where you would take this?

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    RIGHT(COLUMN_NAME, LEN(COLUMN_NAME) -2) as Trimmed
    This would remove the 2 characters leading the ID on the old table.

    Code:
    WITH CTE_OLD
    AS
    (
    SELECT RIGHT(old.ID, LEN(old.ID)-2) as Trimmed
    FROM OLD.TABLE
    WHERE (If you want a condition)
    )
    
    SELECT Trimmed
    FROM CTE_OLD as O
    JOIN NEWTABLE as N
    ON N.ID = O.Trimmed

    Does this work?

    Sorry pretty busy but I wanted to try to toss out an idea to see if it works.

    Kind regards
    Last edited by VLOOKUP; 02-24-15 at 16:27.

  6. #5
    Join Date
    Feb 2012
    Posts
    188
    @VLOOKUP --- Yes that works perfectly! IDK why I was trying to over complicate the situation!

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Glad it works!

    I over complicate things all the time! Anxiety + Coffee = over engineering!

  8. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Alternative is to use SubString:
    Code:
    SubString(column_name, 3, 50)
    Where:
    3 = the first character you want to include (i.e. skip the first two)
    50 = the length of the field definition [or higher] (e.g. if column_name is a varchar(937), use 937+)
    George
    Home | Blog

  9. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The answers provided by gvee and VLOOKUP do exactly the same thing and should produce exactly the same results, but gvee's code is marginally more efficient. For large numbers (millions) of rows this would matter, but for nearly every other purpose the answers are equivalent.

    A special nod to VLOOKUP for taking the time to think the problem through and respond clearly and correctly. Welcome to the (very small) group of contributors to DBForums!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #9
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Exciting times Pat! Any chance I can help I will do it. No questions asked!

    And as Pat mention if you have millions of rows you may want to insert into #temp table and reference that instead of using a CTE. My CTE's run very slow with millions of rows returning.

    Kind regards
    V
    Last edited by VLOOKUP; 02-25-15 at 14:51.

  11. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ooh, had another idea:
    Code:
        ON old.column_name LIKE '[A-Z]\_' + new.column_name ESCAPE '\'
           -- If an integer then Cast(new.column_name As varchar(11))
    But now we're just being fancy :P
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •