CODEWITHSUNDEEP
sql-servert-sqluser-defined-functions
JMS49
JMS49

Reputation: 273

returning a concatenated value from a TSQL scalar function

I am writing 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.

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 input with leading zeros. 

ALTER FUNCTION dbo.FN_ZeroPad
(
 @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 @return
End

Upvotes: 2

Views: 9830

Answers (4)

user2996868
user2996868

Reputation: 21

RIGHT('0000000000000'+[field], 13) AS aliasName

Upvotes: 0

p.campbell
p.campbell

Reputation: 100567

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

Upvotes: 2

gbn
gbn

Reputation: 432210

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

Upvotes: 2

Tim Coker
Tim Coker

Reputation: 6524

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

Upvotes: 1

Related Questions