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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page

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]

Go to Top of Page

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"
Go to Top of Page

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]

Go to Top of Page

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"
Go to Top of Page

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?
Go to Top of Page

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"
Go to Top of Page

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]

Go to Top of Page

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"
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -