Create a User Define Function in SQL:
****************************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CleanQuotes] (@string VARCHAR(500))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @return BIT
DECLARE @position INT
SET @position = 1
DECLARE @output VARCHAR(500)
SET @output = ''
DECLARE @finalOutput VARCHAR(500)
SET @finalOutput = ''
DECLARE @singleQuoteCount INT
SET @singleQuoteCount = 0
WHILE @position <= DATALENGTH(@string) BEGIN IF ASCII(SUBSTRING(@string, @position, 1)) = 39 SET @singleQuoteCount = @singleQuoteCount + 1 ELSE SET @singleQuoteCount = 0 IF @singleQuoteCount <= 1 SET @output = @output + SUBSTRING(@string, @position, 1) IF @position > DATALENGTH(@string)
GOTO ProcessBackQuote
ELSE
SET @position = @position + 1
END
SET @output = REPLACE(@output, Char(39), Char(96))
ProcessBackQuote:
SET @position = 1
SET @singleQuoteCount = 0
WHILE @position <= DATALENGTH(@output) BEGIN IF ASCII(SUBSTRING(@output, @position, 1)) = 96 SET @singleQuoteCount = @singleQuoteCount + 1 ELSE SET @singleQuoteCount = 0 IF @singleQuoteCount <= 1 SET @finalOutput = @finalOutput + SUBSTRING(@output, @position, 1) IF @position > DATALENGTH(@output)
GOTO ExitUDF
ELSE
SET @position = @position + 1
END
ExitUDF:
RETURN @finalOutput
END
****************************************************************
Use the function to replace the quotes in fields:
UPDATE TABLE_NAME SET FIELD_NAME = dbo.CleanQuotes(FIELD_NAME);
.
