SQL Not Like Statement

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

cust开发者_Go百科omer2

customer3

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

LIKE (Transact-SQL)

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

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