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);