I have a scenario where i have to find the matching string from a table.
i used a self join query using substr and length to compare the strings.
SELECT T1.*,T2.* FROM TABLE T1,TABLE T2WHERE T1.ID= T2.ID AND T1.DATE= T2.DATE AND T1.SYSTEM = 'ORCL' AND T2.SYSTEM = 'TER' AND SUBSTR(T1.NAME,1,5) = SUBSTR(T2.NAME,1,5); TABLE ID DATE NAME SYSTEM 1 SYSDATE TEST ORCL 1 SYSDATE @# TEST TER
2 SYSDATE coca cola ORCL
2 SYSDATE ATLANTA coca cola TER
ID DATE NAME SYSTEM ID_1 DATE_1 NAME_1 SYSTEM_1 1 SYSDATE TEST ORCL 1 SYSDATE '@#TEST' TER
2 SYSDATE coca cola ORCL 1 SYSDATE ATLANTA coca cola TER
Not 100% clear on the question. Obviously the SUBSTR equality comparison does not give the indicated output. This would, but may not address the real requirements:
… AND T2.NAME LIKE '%'||SUBSTR(T1.NAME,1,5)||'%'
@Fred My requirement is to match name if its close or similar betwen systems 'ORCL' and 'TER' for the same ID.
so here NAME is coca cola for 'ORCL' and Atlanta Coca Cola for 'TER' as both belongs to same name the representation is different in ORCL and TER.
i need to identify these names using some logic which are closer or similar and Exact match.