[vivek_vdc]

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.

[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

[vivek_vdc]

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

[milindoke]

oops!

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

but the logic should be the same

[r937]

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+'%'