SET QUOTED_IDENTIFIER OFF 
   DECLARE @Toff VARCHAR(100)
   SET @Toff = 'I''ve got a lovely bunch of cocnuts'
   SELECT @Toff

CREATE TABLE #images (files VARCHAR(500) NULL, InsertCMD
AS 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) '
+ ' SELECT ' + LEFT(files, PATINDEX('%[^0-9]%', files) - 1) + ' AS ImageID, '
+ '''' + files + ''' As ImageName, '
+ 'BulkColumn FROM OPENROWSET( Bulk '
+ '''D:\MijnDocumenten\DWCoureurLocaleData\Irfanview_20112011\' + files + ''','
+ ' SINGLE_BLOB) AS BLOB '
)

SELECT 'Giro d''Italia'
UNION
SELECT 'This is an empty string: '''''

output:

---------------------------
Giro d'Italia
This is an empty string: ''