Search a string pattern

Database

Search a string pattern

Hi,

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

OUTPUT:

  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

 

2 REPLIES 2
Teradata Employee

Re: Search a string pattern

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)||'%'

 

Re: Search a string pattern

@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.