CODEWITHSUNDEEP
t-sqlcharvariable-length
Chris
Chris

Reputation: 439

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

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.

Upvotes: 0

Views: 3758

Answers (1)

djangojazz
djangojazz

Reputation: 13242

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')

Upvotes: 2

Related Questions