CODEWITHSUNDEEP
sqlsql-server-2012-express
Deina Underhill
Deina Underhill

Reputation: 567

Basic SQL UDF creation

After reading far too much on the subject, I'm clueless how to write a UDF, and where to save it.

Instead of writing the same nested REPLACE() numerous times in several other scripts, I would like to be able to call it as I need, something like:

Targ.Name =  dbo.fn_add_sym( isnull(Targ.Name,  Src.Name) )

I have the following function, but I haven't been able to test it, since I don't know where to put it.

CREATE FUNCTION dbo.fn_add_sym( @string NVARCHAR(max) )
RETURNS NVARCHAR(max)
WITH SCHEMABINDING
AS
begin
return 
  @string = REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(
                    REPLACE(
                      REPLACE(@string, N'%2b', N'+')
                    , N'%2d', N'-')
                  , N'%3d', N'=')
                , N'%22', N'"')
              , N'%5f', N'_')
            ,'"', N'"')

end

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

Here is a SQL Fiddle that shows the function.

The following is some code to test it:

select dbo.fn_add_sym('abc%5fdef')

The only syntax error you had was return @string =. The @string = is unnecessarily.

Upvotes: 1

Related Questions