How Do I Escape Apostrophes in Field Valued in SQL Server?

Question

I asked a question a couple days ago about creating INSERTs by running a SELECT to move data to another server. That worked great until I ran into a table that has full on HTML and apostrophes in it. What's the best way to deal with this? Lucking there aren't too many rows so it is feasible as a last resort to 'copy and paste'. But, eventually I will need to do this and the table by that time will probably be way too big to copy and paste these HTML fields.

This is what I have now:

select 'Insert into userwidget ([Type],[UserName],[Title],[Description],[Data],[HtmlOutput],[DisplayOrder],[RealTime],[SubDisplayOrder]) VALUES ('
    + ISNULL('N'''+Convert(varchar(8000),Type)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),Username)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),Title)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),Description)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),Data)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),HTMLOutput)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),DisplayOrder)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),RealTime)+'''','NULL') + ','
    + ISNULL('N'''+Convert(varchar(8000),SubDisplayOrder)+'''','NULL') + ')' 
    from userwidget 

Which is works fine except those pesky apostrophes in the HTMLOutput field. Can I escape them by having the query double up on the apostrophes or is there a way of encoding the field result so it won't matter?

Answer 1

You should use parameters instead of injecting values into your SQL query.

Best Regards,
Oliver Hanappi

Answer 2

You can replace the single apostrophe with double apostrophes.

ISNULL('N'''+ REPLACE(Convert(varchar(8000),Type), '''', '''''') + '''','NULL') + ','

Answer 3

Use QUOTENAME function

declare @valueAsNull as varchar(10)
set @valueAsNull = quotename('NULL','''')

SELECT 'Insert into userwidget ([Type],[UserName],[Title],[Description],[Data],[HtmlOutput],[DisplayOrder],[RealTime],[SubDisplayOrder]) VALUES ('  +
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(Type,'NULL'),''''), @valueAsNull,'NULL') + ', '          
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(Username,'NULL'),''''), @valueAsNull,'NULL') + ', '
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(Title,'NULL'),''''), @valueAsNull,'NULL') + ', '             
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(Description,'NULL'),''''), @valueAsNull,'NULL') + ', '       
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(Data,'NULL'),''''), @valueAsNull,'NULL') + ', '      
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(HTMLOutput,'NULL'),''''), @valueAsNull,'NULL') + ', ' 
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(DisplayOrder,'NULL'),''''), @valueAsNull,'NULL') + ', '      
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(RealTime,'NULL'),''''), @valueAsNull,'NULL') + ', '      
    REPLACE(QUOTENAME(Convert(varchar(8000),ISNULL(SubDisplayOrder,'NULL'),''''), @valueAsNull,'NULL') + ')'        
FROM userwidget