SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Enhancement of PARSENAME function
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 12/07/2006 :  07:37:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See improved version below posted 06/22/2007 : 20:52:45

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/17/2009 12:27:17

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 12/07/2006 :  07:45:17  Show Profile  Visit robvolk's Homepage  Reply with Quote
Nice!

Can't wait for Igor's trashing comments.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 12/07/2006 :  08:09:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30242 Posts

Posted - 12/07/2006 :  20:05:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 07/17/2009 12:28:02
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 04/21/2007 :  05:23:29  Show Profile  Reply with Quote
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

Sweden
30242 Posts

Posted - 06/22/2007 :  20:02:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 07/17/2009 12:22:46
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 06/22/2007 :  20:52:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 06/25/2007 14:07:58
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 06/22/2007 :  21:00:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/20/2007 :  10:27:12  Show Profile  Reply with Quote
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)

Singapore
17635 Posts

Posted - 12/30/2007 :  22:51:52  Show Profile  Reply with Quote
Peter,

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


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


KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 01/01/2008 :  15:12:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Singapore
17635 Posts

Posted - 01/01/2008 :  20:08:18  Show Profile  Reply with Quote
Thanks. But it should be
IF @NextPos <= @LastPos




KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 01/02/2008 :  02:13:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Great!
Thanks for the feedback.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

BorisCallens
Yak Posting Veteran

Belgium
50 Posts

Posted - 04/30/2008 :  03:17:47  Show Profile  Reply with Quote
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

Sweden
30242 Posts

Posted - 11/12/2008 :  04:53:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Singapore
17635 Posts

Posted - 07/17/2009 :  11:59:44  Show Profile  Reply with Quote
Peter,

can you update fnParseList to handle this ?

thanks


KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 07/17/2009 :  12:24:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Done. Thank you.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/07/2010 :  10:34:32  Show Profile  Reply with Quote
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 - 08/24/2010 :  17:33:04  Show Profile  Reply with Quote
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)

Singapore
17635 Posts

Posted - 08/24/2010 :  20:04:21  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/12/2013 :  07:02:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000