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?

score 1 · Accepted Answer · answered Apr 14 '22 at 09:21

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;

answered Apr 14 '22 at 09:21

Just a quick question. How to tackle "case-sensitive" situation here? Eg. in case I want to join `salesorder` to `SalesOrder_20220414` using the above technique, how to do that? – pythondumb Apr 15 '22 at 00:36
The quick fix is to use `UPPER(col_1) LIKE UPPER(col_2)` but there are better solutions : see https://stackoverflow.com/questions/2876789/how-can-i-search-case-insensitive-in-a-column-using-like-wildcard – Apr 15 '22 at 04:19

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

1 Answers1