Using a name with an embedded single quote in a select statement

[#1]

I want to use a query like this one below

SELECT
     Employee_Id, Work_Email, Department,City,Job_Title,Reports_To
FROM
     Employee
WHERE
   EmployeeName = 'Firstname O'Lastname'

I've tried combinations of using single and double quotes, using [] and also trying to use CHR(39) but nothing seems to get this to work properly....what do I do if the name has an embedded single quote?

[rwunderlich]

What brand of database is this query going to?

[#3]

Its a SQL server database

[Gysbert_Wassenaar · Accepted solution]

You can escape the single quote with another single quote: EmployeeName = 'Firstname O''Lastname'

[#5]

Thanks...that worked.

I had thought I had tried all the possible combinations.