Wednesday, February 17, 2010

Replace Single Quotes SQL Stored Procedure User Defined Function

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);





.

Check This Out!

More Links to Good Information