How to check if a string from 1st table is present in 2nd table with different extension?-CodePudding

I have two tables and the first table contains a column with filenames with format 'Filename.error'. The second table contains filenames with the format 'Filename.tm'. I want to see if the filenames from the first table are present in the 2nd table with .tm format. How can it be accomplished?

CodePudding user response：

Try this query to get only the 'Filename' and similarly use it for other table to get only 'FileName', then use join to get the common tables

SELECT  FileNameColumn
        ,SUBSTRING(FileNameColumn,-6,Len(FileNameColumn))
        AS  FileNameColumn_trimmed
FROM 
        TableName

CodePudding user response：

Remove the last 5 characters (ie "error"), then add "tm" and search for that:

select
  a.filename,
  case when exists(
    select *
    from table2 b
    where b.filename = substr(a.filename, 1, len(a.filename) - 5)   'tm'
  ) then 'Y' else 'N' end as tm_exists
from table1