SQL Not Like Statement

Question

How can I select table from sys.tables where table name does not containt special word (which pass in parameters).

I want to select all tables where its contains word 'customer' but not those who ends with 'old' in name.

TableName In DB

customer1
customer2
customer3
customerold1
customerold2

Output Wanted

customer1
customer2
customer3

Answer 1

SELECT * FROM sys.tables
    WHERE TableName LIKE '%customer%'
      AND TableName NOT LIKE '%old' -- note the lack of trailing '%'

LIKE (Transact-SQL)

Answer 2

Assuming that you have a parameter for your special word, you can do:

WHERE TableName LIKE @specialWord + '%'
AND TableName NOT LIKE '%' + @specialWord + '%old%'