returning a concatenated value from a TSQL scalar function

Question

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

p.campbell · Accepted Answer · 2010-06-30 17:50:40Z

2

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

edited Jun 30, 2010 at 17:50

answered Jun 30, 2010 at 17:07

p.campbell

101k70 gold badges264 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JMS49 Over a year ago

That Works! That is so much easier than I was going about it. Thanks so very much for your response!

iheartcsharp Over a year ago

As stated by gbn, no need to use the REPLICATE() function.

p.campbell Over a year ago

@user78739 thanks for your input on a question that's 2.5 years old.

gbn · Accepted Answer · 2010-06-30 17:28:15Z

2

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

answered Jun 30, 2010 at 17:28

gbn

435k84 gold badges602 silver badges690 bronze badges

Comments

Tim Coker · Accepted Answer · 2010-06-30 17:11:39Z

1

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

answered Jun 30, 2010 at 17:11

Tim Coker

6,5242 gold badges34 silver badges65 bronze badges

2 Comments

JMS49 Over a year ago

cool, I have an answer that met the need, but I very much appreciate this input too. I think this will be very useful too.

Tim Coker Over a year ago

Check out UDFs, though. If you're new to T-SQL, they are extremely useful as you can use them as a column in a select statement which opens up many new avenues of use.

Tobia Zambon · Accepted Answer · 2013-11-15 15:59:42Z

0

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

edited Nov 15, 2013 at 15:59

Tobia Zambon

7,6593 gold badges42 silver badges70 bronze badges

answered Nov 15, 2013 at 15:42

user2996868

211 bronze badge

1 Comment

Mark Over a year ago

You might want to add just a little bit of text to your answer to explain how this solves the problem and perhaps also why you think this is a better answer than any previously given answers, including the one accepted by OP.

Collectives™ on Stack Overflow

returning a concatenated value from a TSQL scalar function

4 Answers 4

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related