Using a function to parse/return the Nth field of a delimited column

  • Comments posted to this topic are about the item Using a function to parse/return the Nth field of a delimited column

  • 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

  • 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.

  • Thanks for the script.

  • 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

  • Oops posted in the wrong spot

    • This reply was modified 4 months, 3 weeks ago by  Steve Collins.

    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