Parse Text Variable in UDF

Question

Post reply

Parse Text Variable in UDF

Megan Kelly

Say Hey Kid

Points: 667
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: 1000539 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://qa.sqlservercentral.com/articles/TSQL/62867/

http://qa.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)
Intro to Tally Tables and Functions

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://qa.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: 1000539 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)
Intro to Tally Tables and Functions

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]