SplitQuoteSafe.sql

/****** Object:  UserDefinedFunction [dbo].[SplitQuoteSafe]    Script Date: 09/13/2010 16:57:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- SELECT * FROM dbo.SplitQuoteSafe('"Test","Stuff, that, has commas","ok",24.45,,yes', ',')
CREATE FUNCTION [dbo].[SplitQuoteSafe]
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	DECLARE @inQuote bit
	DECLARE @curPos int
	DECLARE @nextSplit int
	DECLARE @nextQuote int

	Set @Cnt = 1
	SET @inQuote = 0
	SET @curPos = 1

	While (Charindex(@SplitOn, @RowData, @curPos) > 0)
	Begin

		SET @nextSplit = Charindex(@SplitOn, @RowData, @curPos)
		SET @nextQuote = Charindex('"', @RowData, @curPos)

		IF @nextQuote = 0
		BEGIN
			SET @nextQuote = LEN(@RowData) + 1
		END
		
		IF (@nextQuote < @nextSplit AND @inQuote = 0)
		BEGIN
			SET @inQuote = 1
			SET @curPos = @nextQuote + 1
		END
		ELSE IF (@nextQuote < @nextSplit AND @inQuote = 1)
		BEGIN
			SET @inQuote = 0
			SET @curPos = @nextQuote + 1
		END
		ELSE IF (@nextQuote > @nextSplit AND @inQuote = 0)
		BEGIN

			Insert Into @RtnValue (data)
			Select 
				Data = ltrim(rtrim(Substring(@RowData,1,@nextSplit-1)))

			Set @RowData = Substring(@RowData,@nextSplit+1,len(@RowData))
			Set @Cnt = @Cnt + 1
			SET @curPos = 1

		END
		ELSE IF (@nextQuote > @nextSplit AND @inQuote = 1)
		BEGIN
			SET @curPos = @nextSplit + 1
		END

	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END