T-SQL UDF scalar function to return char of variable length

Question

I wish to write a User Defined Function that will return a char value that is sized according to an input parameter. I'm not sure it is even possible, but I thought I'd ask anyway.

So I have a query that looks like this:

SELECT 
Convert(char(10), SUBSTRING('Field1', 1, 10)) AS Field1,
Convert(char(20), SUBSTRING('Value2', 1, 20)) AS Field2

I'd like to write a function that will make it possible to write this:

SELECT 
fn_ConvertAndPad('Field1', 10) AS Field1,
fn_ConvertAndPad('Field2', 20) AS Field2

Is there a way to specify the output as char(10) or char(20)?

This was my (obviously failed) attempt:

ALTER FUNCTION [dbo].[fn_ConvertAndPad] 
(
    -- Add the parameters for the function here
    @input varchar(100),
    @length int
)
RETURNS char
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result char

    -- Add the T-SQL statements to compute the return value here
    DECLARE @blanks char(100)
    SET @blanks = ' '
    SELECT @Result = SUBSTRING(@input + @blanks, 1, @length)

    -- Return the result of the function
    RETURN @Result
END

And no, I can't just return a varchar, I need a padded string for a flat text output.

I think your initial query is going to be the most efficient way to handle this. Function return values need to be typed unless you use Variant, which as you mention in the stored proc answer still needs to be typed afterward. — Data Masseur, Jan 25 '13 at 15:39

djangojazz · Answer 1 · 2013-01-25T17:40:20.217

I am not sure if you can do this with a user function as in my estimation you are going to need to do dynamic SQL to get what you want which is to define a return type during the operation. You can do this in a procedure but not in a function as far as I know.

create proc [dbo].[p_ConvertAndPad] 
(
    @input varchar(100),
    @length int
)
AS


BEGIN

    declare @SQL varchar(256);

    set @SQL =  'DECLARE @Result char(' + cast(@length as varchar) + ');

SELECT @Result = SUBSTRING(''' + @input + ''', 1, ' +  cast(@length as varchar) + ')

Select @Result as [char(' + cast(@length as varchar) + ')]'

    Exec (@SQL)
END
GO

exec p_ConvertandPad 'Whoa I can change dynamically',  10  -- Change number to any number you want and it should change the return type.

EDIT NOT SURE IF THIS IS EXPLICIT ENOUGH FOR YOU BUT CLOSE:

create function [dbo].[f_ConvertAndPad] ( @string varchar(256), @len int)
returns sql_variant
as 

BEGIN
  declare @var sql_variant;

  select @var = cast(left(@string, cast(SQL_VARIANT_PROPERTY(@len,'maxlength') as int)) as char);

  return @var

END
GO

select [dbo].[f_ConvertAndPad]('Hey what are you looking at', 3), sql_variant_property([dbo].[f_ConvertAndPad]('Hey what are you looking at', 3), 'basetype')

Yes, in a stored proc dynamic SQL does the job, but you can't do that in a UDF. I also looked at returning a sql_variant type, but you also have to cast that after. Which doesn't help much. — Chris, Jan 25 '13 at 01:01
It has the same problem, the return type isn't strongly typed so it has to be cast in the consuming query if you wish to do downstream processing. Thanks for the effort though. — Chris, Jan 25 '13 at 18:47
no prob, well good luck on your hunt. Maybe someone else can do it, I don't know if you can do explicit type casts with lengths dynamically in a function. It would be nice if you get an answer to this as I would like to know myself if you can do it. Dynamic SQL is a big crux of functions in my experience with scalar and table functions. — djangojazz, Jan 25 '13 at 20:39

T-SQL UDF scalar function to return char of variable length

1 Answers1