String function to remove double quotes used as text qualifier (enclosed delim token gets also removed))

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FN_REMOVE_DOUBLE_QUOTES_AND_SEMICOLON_IF_NECESSARY]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FN_REMOVE_DOUBLE_QUOTES_AND_SEMICOLON_IF_NECESSARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FN_REMOVE_DOUBLE_QUOTES_AND_SEMICOLON_IF_NECESSARY](@strValue varchar(2000), @DelimToken as varchar(3))
RETURNS varchar(2000)
AS
BEGIN
  DECLARE @endQuote int
  DECLARE @tmpColumn varchar(1000)
  DECLARE @tmpLength int
  DECLARE @finalResult as varchar(1000)
  DECLARE @count int
  DECLARE @odd int
  
  -- determine if number of double quotes in string is odd
  SET @count = LEN(@strValue) - LEN(REPLACE(@strValue,'"',''))
  SET @odd = @count % 2	

  -- set default result = given string
  SET @finalResult = @strValue
  
  -- check if double quotes have to be removed and if delim token exists
  IF CHARINDEX('"', @strValue) <> 0 AND CHARINDEX(@DelimToken, @strValue) <> 0 AND @odd = 0  BEGIN
	-- set default to '' (empty string)
	SET @finalResult = ''
    SET @strValue = @strValue + @DelimToken 
	
	-- loop to separate columns and replace 
	-- double quotes and semicolon
	WHILE (CHARINDEX(';', @strValue) <> 0) BEGIN
	  SET @endQuote = CHARINDEX(@DelimToken, @strValue, 1)
      SET @tmpColumn = SUBSTRING(@strValue, 1, @endQuote) 
      SET @count = LEN(@tmpColumn) - LEN(REPLACE(@tmpColumn,'"',''))
      
      IF @count = 1 BEGIN
        SET @endQuote = CHARINDEX('"', @strValue, @endQuote)
        SET @endQuote = CHARINDEX(@DelimToken, @strValue, @endQuote)
      END
      SET @tmpColumn = SUBSTRING(@strValue, 1, @endQuote) 

      -- replace semicolon and double quotes
      SET @tmpColumn = REPLACE(@tmpColumn, ';', '')
      SET @tmpColumn = REPLACE(@tmpColumn, '"', '')

      -- build string for while condition
      SET @tmpLength = LEN(@strValue)
      SET @tmpLength = @tmpLength - @endQuote + 1
      SET @strValue = SUBSTRING(@strValue, @endQuote + 1, @tmpLength)  
      -- add column to result		
      SET @finalResult = @finalResult + @tmpColumn + @DelimToken 
    END
	SET @finalResult = LEFT(@finalResult,LEN(@finalResult)-1)
  END

  -- assuming there is only one column so all double quotes
  -- can be removed --> no column delimiter could be found
  IF CHARINDEX('"', @strValue) <> 0 AND CHARINDEX(@DelimToken, @strValue) = 0 BEGIN
    SET @strValue = REPLACE(@strValue, '"', '')
    SET @finalResult = @strValue
  END
  RETURN @finalResult
END

GO

Comments