Beginner question on how to use a user defined function

Transact-SQL
1

Hi there,
Firstly can I say please dont assume any knowledge here (DoDo alert!)
I have an old CRM database that has emails in what I believe is an image field completely surrounded by loads of HTML. The field is called RFC822

Someone gave me a query to access it as plain text

SELECT   Top 100  LINKRECID, FLAGS, USERID, FOLDER, FOLDER2, ACCOUNTNO, CREATEON, MAILSIZE, MAILDATE, MAILTIME, MAILREF, LOPRECID, MAILID, EXT,
     dbo.udf_StripHTML1 (  CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(4000))) AS RFC822, recid FROM         dbo.MAILBOX Where ext = 'eml'

This works fine but the HTML is still there.
So, they gave me some script to create a function that strips the HTML. it's called udf_StripHTML1
I know I have created it correctly becasue I see it in the prgammability folder in my database

My question is how to use the function in my query above.

I have tried the below. The query runs but the output is the same as in the first query

SELECT   Top 100  LINKRECID, FLAGS, USERID, FOLDER, FOLDER2, ACCOUNTNO, CREATEON, MAILSIZE, MAILDATE, MAILTIME, MAILREF, LOPRECID, MAILID, EXT,
     dbo.udf_StripHTML1 (  CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(4000))) AS RFC822, recid FROM         dbo.MAILBOX Where ext = 'eml'

Am i putting it in the wrong place?
The query window doesnt seem to recognise the function

2

Welcome

Can you please provide sample data of the embedded html and the function details if possible

1 Like
3

Maybe try Unicode chars, since it's only 4000 byte string not 8000:
...
dbo.udf_StripHTML1 ( CAST(CAST(RFC822 AS varbinary(MAX)) AS nvarchar(4000))) AS RFC822,
...

1 Like
4

Can you provide the code for that function? There are 3 function types - scalar function, inline-table valued function and multi-statement table function.

Depending on how you created the function - it will either be used in the SELECT list or as a table.

And - when you say it isn't recognized, is that only in the query window - or do you get an error when you try running the code?

2 Likes
5

Thanks I realised my mistake and got it working

6

"Working" and "Working Well" are two different things. Judging by the way the UDF is being used, it's a Scalar Function, which is automatically at least 7 times slower than a properly written iTVF (inline Table Valued Function).

Please post the function and one of us will show you how to convert it. It's a chance for a "newbie" to learn something a whole lot better. :smiley:

1 Like