Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    90

    Unanswered: Compare Two Columns with WildCard

    I am trying to compare columns in two tables with a wildcard character.

    One table: Other Table:
    ----------------------
    ID Col1 | ID Col2
    ---------------------
    1 1 1 1A
    1 1B
    2 2 2 2A
    3 3 3 3A
    4 5 4 5A
    4 5B
    4 5C
    5 7
    6 27
    7 50 7 50A
    -------------------------------
    I want to writing something like:

    SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
    From Table1, Table2
    WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)

    which obviously does not work.

    basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:

    1A LIKE '1*'
    1B LIKE '1*'

    How can I do a comparison like this?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I am reminded of an old saying:
    "Make it possible for programmers to write programs in English, and you will find that programmers cannot write in English."
    Care to try that explanation again? Once more, with feeling...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Posts
    90
    ha, sorry about that. my formatting is all messed up above too, i'm sure that didn't help either.

    I basically just want to write a SELECT query and compare two columns with a wildcard character.

    how do I do this?

    do something like:

    table1.col1 LIKE 'sam%'

    except with another column like:

    table1.col1 LIKE '(table2.col2)%'

    except that doesn't work... can I do this?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Table1.ID
         , Table1.Col1
         , Table2.ID
         , Table2.Col2
      from Table1
    left outer
      join Table2
        on Table2.ID = Table1.ID
       and Table1.Col1 like Table2.Col2 + '%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    create table #t1 (id int, c1 int)
    insert into #t1 select
    1, 1 union all select 
    2, 2 union all select 
    3, 3  
    create table #t2 (id int, c1 varchar(10))
    insert into #t2 select
    1, '1A' union all select 
    1, '1B' union all select 
    2, '8A' union all select 
    2, '8B' union all select 
    3, '3' 
    
    select * from #t1 a,#t2 b
    where a.id=b.id
      and b.c1 like convert(varchar(10),a.c1)+'%'
    
    drop table #t1
    drop table #t2
    Last edited by pdreyer; 08-02-06 at 05:34.

Posting Permissions

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