CODEWITHSUNDEEP
sqlms-accesscompare
Juronis
Juronis

Reputation: 495

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

I have table1 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:

enter image description here

And Table2:

enter image description here

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.

Upvotes: 1

Views: 362

Answers (2)

Tony
Tony

Reputation: 10327

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.

Upvotes: 1

Catch22
Catch22

Reputation: 3351

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!

Upvotes: 0

Related Questions