CODEWITHSUNDEEP
sqlingres
Ben Hamilton
Ben Hamilton

Reputation: 985

SQL partial field match using LIKE

I have two tables in an Ingres database table1 and table2. Both contain, amongst other things, the following fields ;-

date_of_birth ingresdate
forename c35
surname c35

I want to match people in one table to the other. The names come from different sources, so aren't identical. Also they are in different cases.

I want all the records in table1 and those in table2 where I can match names

So far I have used the following SQL

SELECT a.forename, a.surname, a.birth_date, b.forename, b.surname
FROM table1
LEFT JOIN table2 ON c.birth_date = b.birth_date AND left(upper(squeeze(b.name)), 3) = left(upper(squeeze(c.forename )), 3)
AND left(upper(squeeze(b.surname)), 3) = left(upper(squeeze(c.surname )), 3)

This is OK and works well except for cases like the one below

Table1

forename = RAJA MOHAMMAD
surname = ZUBAIR

Table2

forename = MOHAMMAD
surname = ZUBAIR

Occasionally the forenames/middle names are missed off.

How can I say "match where the whole forename in table2 appears somewhere in the forename in table1"?

I kinda wanna say

WHERE squeeze(trim(upper(2.forename))) LIKE '% squeeze(trim(upper(1.forename))) %'

But you can't do this in SQL?

Upvotes: 1

Views: 1010

Answers (1)

Ben Hamilton
Ben Hamilton

Reputation: 985

Answer is obvious now I think about it...

WHERE squeeze(trim(upper(2.forename))) LIKE '%' + squeeze(trim(upper(1.forename))) + '%'

Upvotes: 2

Related Questions