SQL ask 2 tables

[m_peled]

I have 2 tables (names A B)
i need (In SQL stetments) to search from table A if rows in table B
with this conditions:
A.col1 containe 4 char.
B.col1 containe 4 or more char

need all the records that A.col1 IN B.col1
Best Regards,
Yuval

[David Portas]

Here's my guess:

SELECT DISTINCT A.col1
FROM A, B
WHERE LEN(A.col1)=4
AND B.col1 LIKE A.col1+'%'

If that's not it and you need more help then please give us a better
specification as explained here:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

[m_peled]

thanks but,
someting not working
for example
A.col1 = "1142"
B.col1 ="1084 1096 1142 1196"
not show
please help

[Lee Tudor]

SELECT A.col1, B.col1
FROM A, B
WHERE LEN(A.col1)=4
AND B.col1 LIKE '%'+A.col1+'%'

Query efficiency goes right out of the window with this addition though.
Normalisation would be recommended if both A and B are large.

Mr Tea

"m_*****@yahoo.com" <yu******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

thanks but,
someting not working
for example
A.col1 = "1142"
B.col1 ="1084 1096 1142 1196"
not show
please help

[David Portas]

SELECT DISTINCT A.col1
FROM A, B
WHERE LEN(A.col1)=4
AND ' '+B.col1+' ' LIKE '% '+A.col1+' %'

Why do you have multiple values in a string in B.col1? Non-atomic
columns are a violation of good design and it looks like these should
exist as separate rows rather than a delimited list.

--
David Portas
SQL Server MVP
--

[m_peled]

I got the error:

Syntax error converting the varchar value '%' to a column of data type
int

[m_peled]

I see that type of A.col1 is Int

A.col1 = 1142
B.col1 ="1084 1096 1142 1196"

[m_peled]

Ok i find it
thanks alot.