udf_ExtractElementValueFromRawEDI

CREATE FUNCTION dbo.udf_ExtractElementValueFromRawEDI (
    @RawEDI         VARCHAR(MAX),
    @Segment        VARCHAR(10),
    @ElementNumber  INTEGER
)
RETURNS VARCHAR(MAX)
AS
BEGIN;
    -- Note: This code only gets the first instance of a value!


    -- Find the position of the segment
    DECLARE @SegmentStart INT = CHARINDEX(@Segment + '*', @RawEDI);

    -- Find the position of the next newline after the segment
    DECLARE @SegmentEnd INT = CHARINDEX(CHAR(10), @RawEDI, @SegmentStart);

    -- Extract the segment
    DECLARE @SegmentText NVARCHAR(MAX);
    IF @SegmentEnd = 0
        SET @SegmentText = SUBSTRING(@RawEDI, @SegmentStart, LEN(@RawEDI) - @SegmentStart + 1)
    ELSE
        SET @SegmentText = SUBSTRING(@RawEDI, @SegmentStart, @SegmentEnd - @SegmentStart);

    -- Extract the desired element
    DECLARE @ElementStart INT = 1;
    DECLARE @ElementEnd INT;
    DECLARE @CurrentElement INT = 0;

    WHILE @CurrentElement < @ElementNumber
    BEGIN;
        SET @ElementStart = CHARINDEX('*', @SegmentText, @ElementStart) + 1;
        SET @ElementEnd = CHARINDEX('*', @SegmentText + '*', @ElementStart);
        SET @CurrentElement = @CurrentElement + 1;
    END;

    DECLARE @Element NVARCHAR(MAX);
    IF @ElementEnd = 0
        SET @Element = SUBSTRING(@SegmentText, @ElementStart, LEN(@SegmentText) - @ElementStart + 1)
    ELSE
        SET @Element = SUBSTRING(@SegmentText, @ElementStart, @ElementEnd - @ElementStart);

    RETURN @Element;

END;