CODEWITHSUNDEEP
sqlsql-server-2012user-defined-functions
Jondlm
Jondlm

Reputation: 8892

Variable Length NVARCHAR with Scalar-valued Function

Is it possible to create a scalar-valued function that returns a variable length nvarchar using SQL Server 2012? When I say variable length, I want the function to accept an nvarchar of any length and return an nvarchar of the same length.

I am trying to create a helper function that cleans up nvarchar columns using views before putting them into our data warehouse. I'm pretty sure this isn't possible, please correct me if I'm wrong.

My current function (obviously not working due to truncation, etc.): 

create function dbo.fnClean (@input nvarchar)
returns nvarchar as
begin
    declare @return nvarchar

    set @return = ltrim(rtrim(@input))

    return @return
end

Upvotes: 0

Views: 1528

Answers (2)

Bart Czernicki
Bart Czernicki

Reputation: 3683

This would not work, although I would question the goal nvarchar or varchar will already take up variable space...I have written scripts in ETL that figure out the max data type and scale the column results. However, this is if you are super cautious about data types and space (specifically decimal and don't have luxury of enterprise). What is your goal?

Upvotes: 0

anon
anon

Reputation:

No, this isn't possible (and you'd need dynamic SQL to create columns or convert this way on the other side). But if you know in advance what the length is, then you could just track that separately instead of relying on the function. Or use a table-valued function that returns @input AND LEN(@input).

Finally, if this is all this function is doing, it is going to be very expensive. What do you gain out of this that you don't get by just applying LTRIM(RTRIM()) to the input without ever needing to create or reference a function? Scalar functions are not exactly great for performance.

Upvotes: 1

Related Questions