Find similar strings in two tables

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 ***Select FieldA from Table1 where
convert(numeric,right(FieldA,len(FieldA)-1)) in (Select FieldB from
Table2)

Madhivanan|||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+'%')|||robert lassiter (rlassiter@.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, esquel@.sommarskog.se

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

Find Like Records with similar field data

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.examnotes.net ***Hi
See if it helps you
CREATE TABLE #T1
(
col1 VARCHAR(10) NOT NULL
)
INSERT INTO #T1 VALUES ('A12345')
INSERT INTO #T1 VALUES ('A345')
CREATE TABLE #T2
(
col1 VARCHAR(10) NOT NULL
)
INSERT INTO #T2 VALUES ('12345')
INSERT INTO #T2 VALUES ('UU1')
SELECT #T1.*,#T2.* FROM #T1 JOIN #T2 ON
#T1.col1 LIKE '%'+ #T2.col1+'%'
"robert lassiter" <rlassiter@.shaw.ca> wrote in message
news:O6A5SFcYFHA.612@.TK2MSFTNGP12.phx.gbl...
>
> 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.examnotes.net ***|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
First of all, columns are not fields and rows are not records. If you
had known this, you would have constraints on the columns that would
not allow you to have two different formats for the same data element
in the database. Fields do not have constraints, among many other
differences.
This will also show you the ones that do not have a match, if I make
the assumption that the only problem is a leading alpha -- you never
gave complete specs.
SELECT T.bad_designed_column, S.bad_designed_column
FROM Target AS T
LEFT OUTER JOIN
Search AS S
ON S.bad_designed_column = SUBSTRING(T.bad_designed_column, 2);