Replace single quotes in SQL Server

Question

I have this function in SQL Server to replace single quotes   But when I insert a single quote it throws an error on Replace  strip            Create Function  dbo   fn stripsingleQuote        strStrip varchar Max       returns varchar as begin     declare  CleanString varchar Max      SET  var  Replace  strip              return  var end

User · Accepted Answer

You need to double up your single quotes as follows   REPLACE  strip

User · Answer

If you really must completely strip out the single quotes you can do this   Replace  strip              However  ordinarily you d replace   with    and this will make SQL Server happy when querying the database  The trick with any of the built-in SQL functions  like replace  is that they too require you to double up your single quotes   So to replace   with    in code you d do this   Replace  strip                  Of course    in some situations you can avoid having to do this entirely if you use parameters when querying the database  Say you re querying the database from a  NET application  then you d use the SqlParameter class to feed the SqlCommand parameters for the query and all of this single quote business will be taken care of automatically  This is usually the preferred method as SQL parameters will also help prevent SQL injection attacks

User · Answer

I ran into a strange anomaly that would apply here   Using Google API and getting the reply in XML format  it was failing to convert to XML data type because of single quotes    Replace  Strip            was not working because the single quote was ascii character 146 instead of 39  So I used   Replace  Strip  char 146        which also works for regular single quotes char 39  and any other special character

User · Answer

Try escaping the single quote with a single quote   Replace  strip

User · Answer

The striping replacement scaping of single quotes from user input  input sanitation   has to be done before the SQL statement reaches the database

User · Answer

You could use char 39  insert into my table values  hi  my name  char 39   s tim     Or in this case  Replace  strip char 39

User · Answer

I think this is the shortest SQL statement for that   CREATE FUNCTION  dbo   fn stripsingleQuote    strStrip varchar Max       RETURNS varchar Max  AS BEGIN         RETURN  Replace  strStrip            END   I hope this helps

User · Answer

Try this    select replace  colname  char 39  char 39       AS colname FROM   dbo   Db Name     I have achieved the desired result  Example   Input value --   Like   Pat      OR         Want Output -- gt    Like   Pat   OR    using above query achieved the desired result

User · Answer

select  replace   colname            AS  colname FROM   dbo   Db Name

User · Answer

Looks like you re trying to duplicate the QUOTENAME functionality  This built-in function can be used to add delimiters and properly escape delimiters inside strings and recognizes both single   and double   quotes as delimiters  as well as brackets   and

User · Answer

If escaping your single quote with another single quote isn t working for you  like it didn t for one of my recent REPLACE   queries   you can use SET QUOTED IDENTIFIER OFF before your query  then SET QUOTED IDENTIFIER ON after   For example  SET QUOTED IDENTIFIER OFF   UPDATE TABLE SET NAME   REPLACE NAME    S    S     SET QUOTED IDENTIFIER OFF

User · Answer

Besides needing to escape the quote  by using double quotes   you ve also confused the names of variables  You re using  var and  strip  instead of  CleanString and  strStrip

User · Answer

Try REPLACE  strip           SQL uses two quotes to represent one in a string

[sql-server] Replace single quotes in SQL Server

Examples related to sql-server

Examples related to replace

Examples related to error-handling