Call a UDF from Sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Call a UDF from Sql query

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2004-07-29 : 22:27:21

Hi there,

I have a User Defined function that strips HTML out of a text field. Is it possible to call up this UDF in a select statement? i.e something like

Select cleanLongDesc(select description from tblProducts)

which will return the description field in tblProducts without the html tags?

Here is the UDF:

CREATE FUNCTION cleanLongDesc(
@myLongDesc varchar(8000))
RETURNS varchar(8000) -- cleaned long desc
AS
BEGIN

DECLARE @fpos int, @spos int, @myNewDesc varchar(8000)

SET @myNewDesc=@myLongDesc

IF CHARINDEX('<', @myLongDesc) > 0
BEGIN
SET @fpos=CHARINDEX('<', @myNewDesc)

--WHILE CHARINDEX('<', @myNewDesc) > 0
WHILE @fpos > 0
BEGIN
SET @spos=CHARINDEX('>', @myNewDesc)
IF @spos > @fpos
SET @myNewDesc=STUFF(@myNewDesc, @fpos, @spos-@fpos+1, '')
ELSE
SET @myNewDesc=STUFF(@myNewDesc, @spos, 1, '')
SET @fpos=CHARINDEX('<', @myNewDesc)
END

END
RETURN @myNewDesc
END

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-29 : 22:33:02

Sure.

SELECT dbo.CleanLongDesc(description) AS Description From tblProducts

--Ken
Your Kung-Fu is not strong. -- 'The Core'

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-30 : 02:50:00

What's your front-end, matt? I'd rather leave such formatting issues to the client side. That's even better for another reason: you should be able to strip out those HTML tags with a good regular expression in maybe 5 lines of code. That would be faster and more efficient than the loops you got in there.

OS

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-30 : 14:41:07

Your function is probably pretty fast, but for demonstration purposes here is an interesting method of doing the same thing.
Download the regex functions from here:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205[/url]


DECLARE @regex integer
SET @regex = dbo.regexObj( '<(.|\n)+?>', 1, 1 )
SELECT dbo.regexReplace( dbo.regexObjReplace( @regex, description, '' ), ' ', '', 1, 1 ) FROM tblProducts

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources