For your particular scenario, SQL Server will properly evaluate a single quote if you put two quotes in a row like so:

Select * from Category where MDelete =0
And CategoryName Like '%l''atelier%'

I suggest that you use some sort of REPLACE function in your code that will replace any occurrence of a single quote with two single quotes.

For a more robust solution, you may want to go to the Transact SQL help file and investigate the concept of the "Escape" character. This will help you deal with other special characters that might be mis-evaluated in your LIKE clause.