SQL: Match two columns containing NULL values and strings and extract only the matching rows without NULL

Question

I have a temp table FileTable as follows:

  ID FileNameAct                        FileNameString
  1    NULL                             SalesOrderTarget
  2    NULL                             SalesTarget
  3 InventoryMaterialTarget_20220414.xlsx   NULL
  4 InventoryTarget_20220414.xlsx           NULL
  5 SalesOrderTarget_20220412.xlsx          NULL
  6 SalesTarget_20220412.xlsx               NULL

Objective: To match the string between FileNameAct and FileNameString and take out the rows that has very close match.

So the resultant table should look like below:

ID   FileNameAct                       FileNameString
 1  SalesOrderTarget_20220412.xlsx     SalesOrderTarget
 2  SalesTarget_20220412.xlsx          SalesTarget

I am thinking in below line:

    SELECT X.* FROM (SELECT FileNameAct, FileNameString, 
    CASE WHEN ISNULL(FileNameAct,'') LIKE '%'+ ISNULL(FileNameString,'') + '%' THEN 1 ELSE 0 
    END AS Flag     
    FROM @FileTable) X
    WHERE X.Flag=1

Clearly, this would not give the correct result.

Can anybody share any thoughts?

Answer 1

You can use a self-join with conditions

Select 
  b.id,
  a.FileNameAct,
  b.FileNameString
From @FileTable a
Join @FileTable b
On a.FileNameAct 
  like concat(b.FileNameString,'%')
Where b.FileNameString is not null;