Padding in SQL 1

[IanWaterman]

SQL Server 2008

I need to concatenate a String and number field to yield a fixed length string

Currently I have
"vw_CreditLossRatio"."ClaimYear"+(Cast("vw_CreditLossRatio"."ClaimNumber" as varchar) "ClaimRef"

If Claim number is 1 or 340 I get
BK1 or BK340 respectively.

However, I would like to return

BK00001 or BK00340 is that possible.

On searching this site I found following solution, but as this was a 2003 posting I was wondering if there was a neater solution

replicate('0', 5 - len(Cast("vw_CreditLossRatio"."ClaimNumber" as varchar))) + Cast("vw_CreditLossRatio"."ClaimNumber" as varchar)

Thank you

Ian

 

[gmmastros]

As far as I know, there isn't any better method for doing this.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You can try:

."ClaimYear"+ RIGHT('00000' + LTRIM("vw_CreditLossRatio"."ClaimNumber"),5) as  "ClaimRef"

PluralSight Learning Library

 

[IanWaterman]

Markros

Thanks that's much neater.

Did not realise you can use LTRIM with a number, assumed you had to cast as a varchar.

Ian

 

[markros]

I learned it also relatively recently from reading Erland Sommarskog answers in the Transact-SQL MSDN forum.

PluralSight Learning Library

 

[ousoonerjoe]

We have various padding requirements for various bank files, etc. To make it simple, I slapped together a function to manage the Padding with different characters and different lengths:

CREATE FUNCTION PadString(
	@String VARCHAR(255) = ', 
	@PadChar VARCHAR(50), 
	@Length INT = 0)
RETURNS VARCHAR(255)
AS

BEGIN 
	DECLARE @Rtn VARCHAR(255)
	SET @Rtn = SPACE(255 - LEN(@String))
	SET @Rtn = REPLACE(@Rtn,SPACE(1),@PadChar)
	SET @Rtn = @Rtn + @String
	RETURN RIGHT(@Rtn,@Length)
END

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------