SQL Like value from another table and wildcharcters

I have two tables, the value of NDC_10 for table test is a 10 digit code while the value of PRODUCTNDC in table product is a 8 digit code. I am trying to select all of the rows in which the 8 digit code is inside the 10 digit code such as: 0123456789 开发者_运维问答= 10 digits 12345678 = 8 digits

I have come up with something like this logically, but I do not know how to nest the 2 wild characters inside the search of the other table

select NDC_10
FROM test, product
WHERE (NDC_10 LIKE '_product.PRODUCTNDC_')

select t.NDC_10, p.NDC_8
FROM test t
INNER JOIN product p ON (LOCATE(p.NDC_8,t.NDC_10) <> 0)

See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_locate
And: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

And never ever use implicit join syntax, it is an antipattern.
The reason is that it's too easy to do a cross join which in 99,99% of all cases is not what you want.

The wildcard character for LIKE searches is %. So if I wanted to get all records that contained your 8 digit code, I would do this:

SELECT NDC_10
FROM test, product
WHERE NDC_10 LIKE '%12345678%'

If I only want ones that start with the 8 digit code, then this will work:

SELECT NDC_10
FROM test, product
WHERE NDC_10 LIKE '12345678%'

And if I only want records that end with the same 8 digits:

SELECT NDC_10
FROM test, product
WHERE NDC_10 LIKE '%12345678'

Hope that answers your question.

If you're trying to do it across a join, then try this:

SELECT test.NDC_10
FROM test
    INNER JOIN product ON test.NDC_10 LIKE '%' + product.PRODUCTNDC + '%'