Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Question Unanswered: Joining with LIKE?

    Hi,

    I have two tables that I need to join together for a query - the problem is that in one table the column MAY have a leading zero (if it's 4 characters long), and the other table will never have a leading zero.

    An example:
    Code:
    Table1          Table2
    ----------------------
    04232     |       4232
    50292     |      50292
    03211     |       3211
    06843     |       6843
    I was thinking of doing something along the lines of

    LEFT JOIN Table2 ON
    Table2.Column1 LIKE %Table1.Column1

    But I don't think that's possible. Could anyone help me out?

    Thanks!
    -T

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what are the datatype for the two columns?
    it appear you are storing numbers in a VARCHAR2 field in Table1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    It's VARCHAR2 since sometimes they're alphanumeric.

    Quote Originally Posted by anacedent
    what are the datatype for the two columns?
    it appear you are storing numbers in a VARCHAR2 field in Table1

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Yep you can use like for a join i.e.

    select *
    from a, b
    where a.col1 like '%'||b.col2

    Alan

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    or if the leading zero the only issue

    select *
    from a, b
    where a.col1 = ltrim(b.col2,'0');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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