tsql scalar function - need to return a concatenated value

Question

I am new to writing SQL fucntions and trying to write a scalar function in T-SQL. The function should insert leading zeros into an input field of numbers the length of the input can vary in length, i,e,. 123, 1234567, 9876543210 and so forth. The input field is defined as varchar(13)

here is the code that I have written for the function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION dbo.FN_ZeroPad
(
 -- the parameters for the function here
 @FN_Input_No   varchar(13)
)
RETURNS  varchar(13)
AS
BEGIN

declare @FN_PadZero  varchar (13),
  @Return   varchar (13)
Set  @FN_PadZero = '0000000000000'

select @Return =
      Case
   When @FN_Input_No is null then 'Missing'
    When @FN_Input_No < = 0 then '0000000000000'
   When @FN_Input_No > 0 then  (@FN_PadZero + @FN_Input_No)
  else null End

 -- Return the result of the function
 RETURN @return
End

I am not certain that I have defined the fields correctly as I am trying include both an Input variable and an output variable which is new to me. I am trying in insert leading zeros in front of input field and return the value inputted with leading zeros.

Answer 1

Try this to pad your input with zero for the string when it's less than 13 chars.

ALTER FUNCTION dbo.FN_ZeroPad
(
 -- the parameters for the function here
 @FN_Input_No   varchar(13)
)
RETURNS  varchar(13)
AS
BEGIN

declare @Return   varchar(13)

  SELECT @return = RIGHT(REPLICATE('0', 13) + @FN_Input_No, 13 ) 

 RETURN @return
End

Answer 2

Your function won't work as written. You're building a string bigger than the size of the variable and when you assign to the @return variable, its going to take the left most characters, so it will always return 0000000000000.

Your best bet is to use this function to do your padding which gives you much more flexibility than what you're trying to use as you can specify how many characters to pad with (up to the length of your return variable) as well as change the padding character):

CREATE FUNCTION [dbo].[PadString] 
(    @Seq varchar(16),
    @PadWith char(1),
    @PadLength int
) 
RETURNS varchar(16) AS

BEGIN 
    declare @curSeq varchar(16)
    SELECT @curSeq = ISNULL(REPLICATE(@PadWith, @PadLength - len(ISNULL(@Seq ,0))), '') + @Seq
    RETURN @curSeq
END

Use of this would be

SELECT dbo.PadString ('13', '0', 5)

User Defined Functions can be used in select statements as another column if needed. They provide greater flexibility in their use than stored procedures at the expense of requiring a single return value, which is what you have in this case, so a UDF fits perfectly.

This function copied from here:

http://blogs.ugidotnet.org/fgiossi/archive/2007/11/15/how-to-format-a-value-using-t-sql.aspx

Answer 3

Just one line needed: no need to bother with LEN or REPLICATE or ISNULL or CONVERT or CAST or local variables...

ALTER FUNCTION dbo.FN_ZeroPad
(
 -- the parameters for the function here
 @FN_Input_No   varchar(13)
)
RETURNS  varchar(13)
AS
BEGIN
    RETURN RIGHT('0000000000000' + @FN_Input_No, 13)
END