Create Column From Delimited String
This SQL function returns a column as a string from the provided delimited string, taking in to account the specified field delimiter and text qualifiers. In my own use if this, I perform a BULK INSERT operation into a table and then build a new table based on previously defined field definitions stored in yet another table. This function should handle most delimitation needs, though I did not spend a lot of time making sure it was fully generic as I had a fairly narrow scope I needed for it to fill.
Inputs:
- @RowText: The delimited string from which the column's data will be extracted.
- @FieldDelimiter: The string that indicates a seperation OF columns.
- @TextQualifier: The string that indicates a text value.
- @Column: The column OF data TO return. The value of @Column must be greater than 0.
Returns:
- The location of one string within another based on the parameterized constraints
Assumes:
- It IS ok FOR a field delimiter TO occur within a field enclosed BY the text qualifiers
- Two text qualifiers, one following the other, IS meant TO indicate a single text qualifier in the output.
- A field that has a starting AND ending text qualifier will have those characters removed FROM the RETURN string.
- A field that begins WITH a text qualifier will END with a text qualifier.
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
Description:
DFCol: Delimited FILE COLUMN
Returns the location OF one string within another with parameterized constraints:
@RowText: The delimited string FROM which the column's data will be extracted.
@FieldDelimiter: The string that indicates a seperation OF columns.
@TextQualifier: The string that indicates a text value.
@Column: The column OF data TO return. The value of @Column must be greater than 0.
MODULE TableName Date/Time
DATE BY CHANGE
12/26/2002 Sean P. O. MacCath-Moran Created
*/
CREATE FUNCTION fn_cnv_GetDelimitedColumn (
@RowText varchar(200),
@FieldDelimiter varchar(10),
@TextQualifier varchar(10),
@Column int)
RETURNS varchar(200)
AS
BEGIN
DECLARE @ColStart int, @EndTQualCol int, @TempString varchar(6000), @TQualFound int, @ColumnsIdentified int, @ColEnd int, @ReturnValue varchar(8000)
/*Make sure we have valid perameters*/
IF @RowText IS NULL OR @FieldDelimiter IS NULL OR @TextQualifier IS NULL OR @Column < 1
BEGIN
/*Cannot perform search WITH these values*/
RETURN NULL
END
IF LEN(@RowText) = 0 OR LEN(@FieldDelimiter) = 0 OR LEN(@RowText) < LEN(@FieldDelimiter)
BEGIN
/*Cannot perform search WITH these values*/
RETURN NULL
END
SET @ColumnsIdentified = 0
SET @TQualFound = 0
SET @ColStart = -1
SET @ColEnd = -1
IF @Column > 0
BEGIN
/*Determine WHERE this COLUMN begins*/
WHILE @ColumnsIdentified < @Column
/*Loop through @RowText AND find the @Column instance OF @FieldDelimiter*/
BEGIN
IF CHARINDEX(@FieldDelimiter, @RowText, @ColStart) = 0 AND @Column != (@ColumnsIdentified + 1)
/*If it IS NOT != THEN this is the last COLUMN AND it is the column being sought*/
BEGIN
RETURN NULL
END
/*An instance OF @FieldDelimiter has been located.*/
IF @ColStart = -1
SET @ColStart = 1
ELSE
BEGIN
SET @ColStart = CHARINDEX(@FieldDelimiter, @RowText, @ColStart)
/*If @ColStart IS 0 here THEN there are no more columns TO find...*/
IF @ColStart = 0
RETURN NULL
ELSE
SET @ColStart = @ColStart + LEN(@FieldDelimiter)
END
SET @EndTQualCol = @ColStart
WHILE SUBSTRING(@RowText,@ColStart,LEN(@TextQualifier)) = @TextQualifier
/*If the @ColStart detected begins WITH a text qualifier...*/
BEGIN
SET @ColEnd = CHARINDEX(@TextQualifier+@FieldDelimiter,@RowText,@EndTQualCol)
/*IF a field AND text qualifier combo IS found THEN USE that position. If not, then if the last
character IN the row isa textqualifier THEN USE that. If not, then something has gone wrong -
RETURN a null*/
IF @ColEnd > 0
SET @ColEnd = @ColEnd + LEN(@TextQualifier)
ELSE IF SUBSTRING(@RowText,LEN(@RowText)-LEN(@TextQualifier)+1,LEN(@TextQualifier)) = @TextQualifier
SET @ColEnd = LEN(@RowText) + 1
ELSE
RETURN NULL
SET @TempString = SUBSTRING(@RowText,@ColStart,@ColEnd - @ColStart)
/*Discover how many occurances OF @TextQualifier exist IN @TempString*/
WHILE 1=1
BEGIN
IF CHARINDEX(@TextQualifier,@TempString,1)>0
BEGIN
SET @TempString = SUBSTRING(@TempString,CHARINDEX(@TextQualifier,@TempString,1) + LEN(@TextQualifier),LEN(@TempString))
SET @TQualFound = @TQualFound + 1
END
ELSE
BREAK
END
/*IF there are an odd number OF @TQualFound THEN this is NOT the END of the column...*/
IF (@TQualFound % 2) > 0
BEGIN
SET @EndTQualCol = @ColEnd
SET @TQualFound = 0
END
ELSE
BREAK
END
SET @ColumnsIdentified = @ColumnsIdentified + 1
IF @ColumnsIdentified < @Column AND @ColEnd > -1
BEGIN
SET @ColStart = @ColEnd
SET @ColEnd = -1
END
END
END
/*Determine WHERE this COLUMN ends IF NOT done so BY the previous steps*/
IF @ColEnd = -1
BEGIN
SET @ColEnd = CHARINDEX(@FieldDelimiter, @RowText, @ColStart)
IF @ColEnd = 0 SET @ColEnd = LEN(@RowText) + 1
END
SET @ReturnValue = SUBSTRING(@RowText,@ColStart,@ColEnd - @ColStart)
/*This line reduces ALL DOUBLE occurances OF @TextQualifier TO single occurances*/
SET @ReturnValue = REPLACE(@ReturnValue,@TextQualifier+@TextQualifier,@TextQualifier)
/*These two lines strip a @TextQualifier OFF of either side of the RETURN value if such a character IS there.*/
IF SUBSTRING(@ReturnValue,1,LEN(@TextQualifier)) = @TextQualifier SET @ReturnValue = SUBSTRING(@ReturnValue,LEN(@TextQualifier)+1,8000)
IF SUBSTRING(@ReturnValue,LEN(@ReturnValue)-LEN(@TextQualifier)+1,LEN(@TextQualifier)) = @TextQualifier SET @ReturnValue = SUBSTRING(@ReturnValue,1,LEN(@ReturnValue)-LEN(@TextQualifier))
IF LEN(@ReturnValue) = 0 SET @ReturnValue = NULL
RETURN @ReturnValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Comments
Post new comment