SQL: How to compare two words by the pieces of them?

I have table1开发者_Python百科 with ID column and table2 with ID number column. ID format for example is "aac557z". I want to compare those ID's by the piece where is number. Table1:

And Table2:

I want to select rows from table1 where the piece of the word is "567" in column ID. Numbers may be different. Maybe someone has an idea? I'm using MS Access 2010. Thanks in advance.

---

If the numeric part of the ID is always in the same place and the same length you can use the MID function to get the number part for the comparison. It's won't be very fast on large tables.

Here's a link to the syntax: http://office.microsoft.com/en-us/access-help/mid-function-HA001228881.aspx

If you are going to do this comparison often it might be better to add another field to your table and populate it with the numeric part of the ID when it is inserted in to the table. Then using that field for your comparison your query should be faster.

---

You could use a user-defined function to extract the number part from the Id and then the rest is fairly trivial.

Here's a function to do the job:

CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END

RETURN @IntNumbers
END

And then to compare, something like this:

SELECT * from TABLE1
JOIN TABLE2
    on dbo.ExtractInteger(TABLE1.ID) = dbo.ExtractInteger(Table2.ID)

If the tables are very large, then this is going to be an expensive query!