| Author |
 Topic  |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 12/07/2006 : 07:37:55
|
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
15559 Posts |
Posted - 12/07/2006 : 07:45:17
|
Nice!
Can't wait for Igor's trashing comments.  |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/07/2006 : 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
Sweden
29138 Posts |
Posted - 12/07/2006 : 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 |
Edited by - SwePeso on 07/17/2009 12:28:02 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/22/2007 : 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 |
Edited by - SwePeso on 07/17/2009 12:22:46 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/22/2007 : 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 |
Edited by - SwePeso on 06/25/2007 14:07:58 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/22/2007 : 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 - 08/20/2007 : 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)
Singapore
16746 Posts |
Posted - 12/30/2007 : 22:51:52
|
Peter,
select *
from dbo.fnParseList(',', 'a')
This does not return anything. Would expect it to return row 'a'
KH
Time is always against us
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/01/2008 : 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)
Singapore
16746 Posts |
Posted - 01/01/2008 : 20:08:18
|
Thanks. But it should be
IF @NextPos <= @LastPos
KH
Time is always against us
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/02/2008 : 02:13:30
|
Great!
Thanks for the feedback.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
BorisCallens
Yak Posting Veteran
Belgium
50 Posts |
Posted - 04/30/2008 : 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
Sweden
29138 Posts |
Posted - 11/12/2008 : 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)
Singapore
16746 Posts |
Posted - 07/17/2009 : 11:59:44
|
Peter,
can you update fnParseList to handle this ?
thanks
KH
Time is always against us
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/17/2009 : 12:24:07
|
Done. Thank you.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/07/2010 : 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 - 08/24/2010 : 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)
Singapore
16746 Posts |
Posted - 08/24/2010 : 20:04:21
|
select *
from #test
cross apply dbo.fnParseList(',', area)
order by cc, Data
KH
Time is always against us
|
|
|
| |
Topic |
|
Enhancement of PARSENAME function
