I want to delete from table all records which selected string value contains in another value from another table (with ignore case sensitive).
For example: if
DELETE FROM [table1]
WHERE value1 LIKE '%' + (SELECT value2 FROM [table2]) + '%'
You could use an exists criteria:
delete from Table1 T1 where exists (select T2.value2 from Table2 T2 where T1.value1 like '%'+T2.value2+'%')