| Author |
 Topic  |
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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 - Peso on 07/17/2009 12:27:17
|
|
|
robvolk
SQLTeam MVY/MIA
USA
12325 Posts |
Posted - 12/07/2006 : 07:45:17
|
Nice!
Can't wait for Igor's trashing comments.  |
 |
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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 - Peso on 07/17/2009 12:28:02 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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 - Peso on 07/17/2009 12:22:46 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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 - Peso on 06/25/2007 14:07:58 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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
13672 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
|
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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
13672 Posts |
Posted - 01/01/2008 : 20:08:18
|
Thanks. But it should be
IF @NextPos <= @LastPos
KH
Time is always against us
|
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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? |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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
13672 Posts |
Posted - 07/17/2009 : 11:59:44
|
Peter,
can you update fnParseList to handle this ?
thanks
KH
Time is always against us
|
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 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
19683 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
8 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
13672 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
