Enhancement of PARSENAME function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Enhancement of PARSENAME function

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 07:37:55

See improved version below posted 06/22/2007 : 20:52:45

Peter Larsson
Helsingborg, Sweden

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-12-07 : 07:45:17

Nice!

Can't wait for Igor's ~~trashing~~ comments.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 08:09:28

Thanks. I thought it needed an improvement since I gave a suggestion the other day that made use of PARSENAME function.
I felt the need for a similar function that could accept more parts and also choose "from left" and "from right".

Haven't heard of the more clever guy since June.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 20:05:47

And just for fun, another Split function built on the same speedy concept (there is only two string manipulations with reverse function).

See improved version below posted 06/22/2007 : 20:02:46

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-21 : 05:23:29

This was quite similar to the original function (get part of a delimited string)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46188

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-22 : 20:02:46

Newer fnParseList function (20-25 percent faster than the original function)

CREATE FUNCTION dbo.fnParseList
(
	@Delimiter CHAR,
	@Text TEXT
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
	DECLARE	@NextPos INT,
		@LastPos INT

	SELECT	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
		@LastPos = 0

	WHILE @NextPos > 0
		BEGIN
			INSERT	@Result
				(
					Data
				)
			SELECT	SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

			SELECT	@LastPos = @NextPos,
				@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
		END

	IF @NextPos <= @LastPos
		INSERT	@Result
			(
				Data
			)
		SELECT	SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

	RETURN
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-22 : 20:52:45

Newer fnParseString function (40-60 percent faster than the original function)

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

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT,
		@Found SMALLINT

	IF @Section > 0
		SELECT	@Text = REVERSE(@Text)

	SELECT	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
		@LastPos = 0,
		@Found = 1

	WHILE @NextPos > 0 AND ABS(@Section) <> @Found
		SELECT	@LastPos = @NextPos,
			@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1),
			@Found = @Found + 1

	RETURN	CASE
			WHEN @Found <> ABS(@Section) OR @Section = 0 THEN NULL
			WHEN @Section > 0 THEN REVERSE(SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END))
			ELSE SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
		END
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-22 : 21:00:32

If you want to emulate PARSENAME function completely, you have to use NULLIF too!

declare @var varchar(200)

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

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

I found this out today, if PARSENAME find an empty string, PARSENAME returns NULL, not empty space.

Peter Larsson
Helsingborg, Sweden

davidagnew37
Starting Member

33 Posts

Posted - 2007-08-20 : 10:27:12

thansk you sooo much. this has been giveing me so much grief. When I have time to night I will try and understand it!!

the second one is perfect!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-30 : 22:51:52

Peter,

select	*
from	dbo.fnParseList(',', 'a')

This does not return anything. Would expect it to return row 'a'

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-01 : 15:12:53

Try replacing this line

IF SCOPE_IDENTITY() > 0

with this line

IF @NextPos < @LastPos

I have not access to my ordinary computer right now (in a flight terminal) so I can't test it.

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-01 : 20:08:18

Thanks. But it should be

IF @NextPos <= @LastPos

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-02 : 02:13:30

Great!
Thanks for the feedback.

E 12°55'05.25"
N 56°04'39.16"

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-30 : 03:17:47

Am I the only one getting an "Argument data type text is invalid for argument 1 of reverse function." error? A quick google tells me the reverse function doesn't accept text.
It points at the "SELECT @Text = REVERSE(@Text)" line.
You could make it REVERSE(CAST (@Text as varchar))

Also, why is the text datatype prefered over the ntext one?
I'm thinking of my website now. It will be localised and contain foreign chars. So it looks like a good idea to me to use ntext, not?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 04:53:56

If you are using SQL Server 2005, replace NTEXT with NVARCHAR(MAX).

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 11:59:44

Peter,

can you update fnParseList to handle this ?

thanks

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-17 : 12:24:07

Done. Thank you.

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-04-07 : 10:34:32

fnParseList seems to only handle the first 8,000 characters under SQL2008 - its fine if I change parameter from TEXT to VARCHAR(MAX) though.

oldfox
Starting Member

17 Posts

Posted - 2010-08-24 : 17:33:04

I am trying to use fnParseList function.
It works great if there with 1 row table, but if a table has 2+ rows, I am getting the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

How to make it work with big tables?

drop table #test
create table #test
(cc int,
area varchar(50))
insert into #test
values (44,'77,78,79')
insert into #test
values (45,'72,73,74,75,76,77')

select *
from dbo.fnParseList(',', (select area from #test )) ,
#test b
order by cc, DATA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-24 : 20:04:21

[code]
select *
from #test
cross apply dbo.fnParseList(',', area)
order by cc, Data[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-11-12 : 07:02:31

This allows for multicharacter delimiter

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

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT,
		@Offset TINYINT = DATALENGTH(@Delimiter),
		@Found SMALLINT;

	IF @Section > 0
		SELECT	@Text = REVERSE(@Text),
			@Delimiter = REVERSE(@Delimiter);

	SELECT	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
		@LastPos = 1 - @Offset,
		@Found = 1

	WHILE @NextPos > 0 AND ABS(@Section) <> @Found
		SELECT	@LastPos = @NextPos,
			@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + @Offset),
			@Found = @Found + 1;

	RETURN	CASE
			WHEN @Found <> ABS(@Section) OR @Section = 0 THEN NULL
			WHEN @Section > 0 THEN REVERSE(SUBSTRING(@Text, @LastPos + @Offset, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - @Offset END))
			ELSE SUBSTRING(@Text, @LastPos + @Offset, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - @Offset END)
		END
END

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources