October 19, 2009 at 11:28 pm
Comments posted to this topic are about the item Using a function to parse/return the Nth field of a delimited column
November 6, 2009 at 11:14 am
There is a slight problem with your first IF statement if you truly intend for the delimiter to be "... any weird combination of characters that you think is unique to the source file..."
The statement would need to be changed to:
IF right(@EntString,LEN(@Delimiter))<>@Delimiter
otherwise your delimiter is limited to 1 character
July 2, 2012 at 9:19 am
Is there any way of adapting this so that the query took for example the 5th value after a certain value came up in a delimited string? With the certain value arising fairly often.
May 23, 2016 at 4:42 pm
Thanks for the script.
January 5, 2023 at 10:57 pm
Here is the updated script that has been in use for several years in a production ETL environment. There were a couple of data conditions that caused an index out of bounds or problems with ragged ending data sets so this seems to have corrected those deficiencies! Enjoy!
CREATE FUNCTION [dbo].[ufn_parsefind]
(
@EntString VARCHAR(MAX),
@Delimiter VARCHAR(10),
@Occurrence BIGINT
)
RETURNS VARCHAR(MAX)
AS
/*
SELECT [PhyNetDW_Admin].[dbo].[ufn_parsefind]('john.burnette.excel.a','.',4)
*/BEGIN
DECLARE @CurString varchar(max)
DECLARE @Pos bigint
DECLARE @Loop BIGINT
IF @EntString IS NULL OR DATALENGTH(@EntString) < 1 OR @Delimiter IS NULL OR DATALENGTH(@Delimiter) < 1 OR CHARINDEX(@Delimiter,@EntString)= 0
BEGIN
SET @CurString = NULL;
END
ELSE
BEGIN
-- REQUIRE DELIMITER AT END OF STRING AND ENSURE TOO FEW CHARACTERS AVOID ERRORS
IF DATALENGTH(@EntString)>DATALENGTH(@Delimiter)
BEGIN
IF RIGHT(@EntString,DATALENGTH(@Delimiter))<>@Delimiter
SET @EntString = @EntString + @Delimiter
END
-- ESTABLISH CORRECT SYNTAX FOR DELIMITER IN PATINDEX FUNCTION
SET @Delimiter = '%' + @Delimiter + '%'
SET @Loop = 1
SET @Pos = PATINDEX(@Delimiter, @EntString)
-- LOOP THROUGH IF DELIMTERS FOUND
IF @Pos = 0 OR DATALENGTH(@EntString)<1
BEGIN
SET @CurString = NULL
END
ELSE
BEGIN
WHILE @Loop <= @Occurrence AND @Pos <> 0 AND DATALENGTH(@EntString)-1>=DATALENGTH(@Delimiter)
BEGIN
SET @Pos = PATINDEX(@Delimiter, @EntString)
SET @CurString = LEFT(@EntString,@Pos-1)
SET @EntString = RIGHT(@EntString,LEN(@EntString)-LEN(@CurString)-1)
SET @Loop = @Loop + 1
END
END
END
-- DEFAULT A NULL FOR BLANK VALUES
IF ISNULL(@CurString,'')='' OR LEN(@CurString)<1 OR @Loop<@Occurrence OR (@Loop=@Occurrence AND DATALENGTH(@EntString)<DATALENGTH(@Delimiter))
SET @CurString = NULL
-- RETURN VALUE
RETURN @CurString
END
GO
January 6, 2023 at 2:32 am
Oops posted in the wrong spot
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply