Like condition Options

[Fza]

Hey guys, I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.

Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'

Basically the code I'm trying only turns up complete matches and not partials. Anyone know what I'm doing wrong or how I can improve it?

CODE

select *
from TEST
where NAME1 like ('%'||NAME2||'%');

[merrillcd_mem]

What about Text? Depending how large the table is, rows, etc. It might be worth looking into.

select *
from TEST
where contains(name2,name1) > 0

Hey guys, I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.

Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'

Basically the code I'm trying only turns up complete matches and not partials. Anyone know what I'm doing wrong or how I can improve it?

CODE

select *
from TEST
where NAME1 like ('%'||NAME2||'%');

[Fza]

What about Text? Depending how large the table is, rows, etc. It might be worth looking into.

select *
from TEST
where contains(name2,name1) > 0

Thanks for pointing me in the right direction, solved it using the instring function.

CODE

select  x.*
,       instr(upper(trim(NAME2)), upper(trim(NAME1)))
from TEST X;

[burleson]

HI
>> select x.*
, instr(upper(trim(NAME2)), upper(trim(NAME1)))

FYI, YOU CAN CREATE AN INDEX ON THIS TOO ( FUNCTIONED-BASED INDEX):

CODE

create index
   mytidx
on instr(upper(trim(NAME2))

[paulogervasio]

CODE

select * from names where REGEXP_LIKE(name2, name);

[]'s