Parse Text Variable in UDF

Question

Parse Text Variable in UDF

Megan Kelly

Say Hey Kid

Points: 697
More actions
July 3, 2008 at 3:42 pm

#189201

I need to parse a text variable via UDF for insert into a table variable used in a stored procedure. Since TEXTPTR is not legal in a UDF I went a slightly different method....however I can't get this to run as a function!! As a stored procedure it works fine, but it never exits the loop as a function...please help!! (For testing I used the @Loop variables and this also works fine)
Function should be able to accept several thousand ID's that are comma delimited...total @String could be as high as 15,000 characters (with comma)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: User Defined Function dbo.fnSplitText Script Date: 7/3/2008 9:01:00 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnSplitText]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnSplitText]
GO
create function dbo.fnSplitText(
@String ntext,
@Delimiter nvarchar (10)
)
returns @List table ([Value] nvarchar(100))
begin
declare @nextpos int, @nextLen int
Declare @PlaceHolder Table (TotLen int, StartLen int)
Insert Into @PlaceHolder
Select datalength(@String), charindex(@Delimiter, @String)
--Find 1st position
Declare @StartPos int
Select @StartPos = StartLen From @PlaceHolder
Insert Into @List
Select Substring(@String, 1, @StartPos - 1)
-- Declare @Loop int
-- Select @Loop = 1
While @StartPos < DataLength(@String) --@Loop < 34
Begin
Select @NextPos = charindex(@Delimiter, @string, @StartPos + 1)
Select @NextLen = charindex(@Delimiter, @String, @nextPos - @StartPos)
Select @StartPos = @NextPos + 1
Insert Into @List
Select Replace(SubString(@String, @NextPos - (@NextLen - 1), @NextLen - 1), @Delimiter, '')
Update @PlaceHolder
Set StartLen = @NextPos
Select @StartPos = StartLen From @PlaceHolder
-- Set @Loop = @Loop + 1
End
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Here is a post that has as good example of a Split function that works very well. It use varchar(MAX) but you can change them to NVarchar(MAX).

As for the Ntext, either convert it before you call the function OR, change this function's @parameter to NText, and then convert that to NVarchar(MAX) inside the function.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Anam Verma SSCertifiable Points: 5365 More actions · Answer 2

rbarryyoung (7/3/2008)
Here is a post that has as good example of a Split function that works very well. It use varchar(MAX) but you can change them to NVarchar(MAX).
As for the Ntext, either convert it before you call the function OR, change this function's @parameter to NText, and then convert that to NVarchar(MAX) inside the function.

Hi RBarry

Can you please give the link to the post?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

rbarryyoung (7/3/2008)
Here is a post that has as good example of a Split function that works very well. It use varchar(MAX) but you can change them to NVarchar(MAX).
As for the Ntext, either convert it before you call the function OR, change this function's @parameter to NText, and then convert that to NVarchar(MAX) inside the function.

You must expect us to use the force on this one... which post, Barry?

I can't help on the TEXT datatype except to say to covert it to VARCHAR(MAX) first... then, try the following articles to find what you want...

http://www.sqlservercentral.com/articles/TSQL/62867/

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

rbarryyoung (7/3/2008)
Here is a post that has as good example of a Split function that works very well. It use varchar(MAX) but you can change them to NVarchar(MAX).
As for the Ntext, either convert it before you call the function OR, change this function's @parameter to NText, and then convert that to NVarchar(MAX) inside the function.

Well rats, what did I do there?

oops, here it is: http://www.sqlservercentral.com/Forums/FindPost520118.aspx

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Heh... It's the helmet, Barry... ya gotta remember to plug in the O₂ 😛 Can't do it all on just coffee, ya know? :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

heh, That would explain the yellow spots I keep seeing... 😀

😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]