Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: SQL LIKE clause..help in comparing tables

    SQL experts:

    I am comparing columns of two tables with a LIKE clause. I am not getting the desired result. Please help.
    Here is the example.

    Table A
    Name........ID
    B C A..........1
    D E F...........2
    A...............3


    Table B

    Name1
    A

    I need all records of Table A that contain alphabet A from Table B. With the query -

    select * from tablea ta , tableb tb
    where
    ta.name like '%' +tb.name1+'%'

    Only record 3 (A....3) is returned. I also need record 1 as it contains alphabet A(B C A). Note that the individual alphabets are separated by spaces.

    Let me know. Thanks.
    Last edited by vivek_vdc; 12-04-03 at 15:31.

  2. #2
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    8
    i tried out with the following

    TAB1
    ======
    Id, Col1
    ----------
    1, A
    2, B C
    3, A B
    4, D E
    5, F A G

    TAB2
    ======
    Col1
    -----
    A

    this is the result
    SQL> SELECT * FROM TAB1, TAB2
    2 WHERE TAB1.col1 LIKE '%'||TAB2.col1||'%'
    3 /

    ID COL1 COL1
    ---------- ---------- ----------
    1 A A
    3 A B A
    5 F A G A

    Elapsed: 00:00:00.00
    SQL>

    looks like its working fine

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    This is not working. It gives me incorrect syntax near |. I am executing the query in SQL Server Query Analyzer.


    Originally posted by milindoke
    i tried out with the following

    TAB1
    ======
    Id, Col1
    ----------
    1, A
    2, B C
    3, A B
    4, D E
    5, F A G

    TAB2
    ======
    Col1
    -----
    A

    this is the result
    SQL> SELECT * FROM TAB1, TAB2
    2 WHERE TAB1.col1 LIKE '%'||TAB2.col1||'%'
    3 /

    ID COL1 COL1
    ---------- ---------- ----------
    1 A A
    3 A B A
    5 F A G A

    Elapsed: 00:00:00.00
    SQL>

    looks like its working fine

  4. #4
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    8
    oops!

    this works on Oracle. about SQL Server, no idea.

    but the logic should be the same

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql server string concatenation is non-standard, it uses the plus sign instead of double pipes

    select * from tab1, tab2
    where tab1.col1 like '%'+tab2.col1+'%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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