PARSENAME Enhancement

,

The ParseName function is very useful for getting parts out of a string of characters between delimiters. But a limitiation is that you only can get four parts out, and the function only accepts dots as delimiters.

The function I have written below overcomes that limitations, and add a new feature to enable "from left" and "from right" character extraction.


Example code

declare    @var varchar(200)

select    @var = 'a.ab.abc.abcd.abcde'

select    @var,
    dbo.fnParseString(4, '.', @var),
    dbo.fnParseString(-4, '.', @var)

CREATE FUNCTION dbo.fnParseString
(
	@Section SMALLINT,
	@Delimiter CHAR,
	@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT,
		@Found SMALLINT
	
	SELECT	@NextPos = 0,
		@Found = 0
	
	IF @Section > 0
		SELECT	@Text = REVERSE(@Text)
	
	WHILE @NextPos <= DATALENGTH(@Text) AND @Found < ABS(@Section)
		SELECT	@LastPos = @NextPos,
			@NextPos =	CASE
						WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
						ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
					END,
			@Found = @Found + 1
	
	IF @Found <> ABS(@Section) OR @Found = 0 OR @Section = 0
		SELECT	@Text = NULL
	ELSE
		SELECT	@Text = SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
	
	RETURN CASE WHEN @Section < 0 THEN @Text ELSE REVERSE(@Text) END
END

Rate

Share

Share

Rate