Find similar strings in two tables

[robert lassiter]

I have two tables in the same SQL database. Both have a similar numeric
field. One field has the same number as the other field but is prefixed
with various letters. I know how to use LIKE but won't know the partial
string I am looking for. I am trying to use LIKE '%' + Field A or
something that will do this.

Eg.

Table 1 Field A is 'A12345"
Table 2 Field B is '12345"

I want to find every record in Table 1 Field A that contains the exact
Field B data.
*** Sent via Developersdex http://www.developersdex.com ***

[Madhivanan]

Select FieldA from Table1 where
convert(numeric,right(FieldA,len(FieldA)-1)) in (Select FieldB from
Table2)

Madhivanan

[Tzvika Barenholz]

Hi Robert
It is much easier to help when given the right information.
Please post your ddl (i.e. your create table statement) so that people
can know the data exactly.
Also be more specifi: are records in table 1 always with one letter
forllowed by 5 digits? or can it be ABC12345DEF ? Just an example.

without this information the best I think I can do is:
select A from T1 where exists
(select B from T2 where A like '%'+B+'%')

[Erland Sommarskog]

robert lassiter (rl*******@shaw.ca) writes:

I have two tables in the same SQL database. Both have a similar numeric
field. One field has the same number as the other field but is prefixed
with various letters. I know how to use LIKE but won't know the partial
string I am looking for. I am trying to use LIKE '%' + Field A or
something that will do this.

Eg.

Table 1 Field A is 'A12345"
Table 2 Field B is '12345"

I want to find every record in Table 1 Field A that contains the exact
Field B data.

Assuming the the numbers are always at the end, this could do:

SELECT * FROM tbl1 a
WHERE EXISTS (SELECT *
FROM tbl2 b
WHERE charindex(b.fieldb, a.fielda) =
len(a.fielda) - len(b.fieldb) + 1)

Note that this is untested.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp