How to Convert an Int to a Zero Padded String in T-Sql

How do I convert an int to a zero padded string in T-SQL?

Declare @MyInt integer Set @MyInt = 123
Declare @StrLen TinyInt Set @StrLen = 8

Select Replace(Str(@MyInt, @StrLen), ' ' , '0')

Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

If the field is already a string, this will work

 SELECT RIGHT('000'+ISNULL(field,''),3)

If you want nulls to show as '000'

It might be an integer -- then you would want

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg '0000' and VARCHAR(4)),4

Formatting Numbers by padding with leading zeros in SQL Server

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

T-SQL - CAST char with leading zeros to int

Mathematically, leading zeros are meaningless, so an Int can't have leading zeros.

If you need to display leading zeroes, you can always convert to varchar and use concatenation with right, like this:

DECLARE @MyVal int = 10;
SELECT RIGHT('00000' + CAST(@MyVal as varchar(5)), 5)

Most efficient T-SQL way to pad a varchar on the left to a certain length?

This is simply an inefficient use of SQL, no matter how you do it.

perhaps something like

right('XXXXXXXXXXXX'+ rtrim(@str), @n)

where X is your padding character and @n is the number of characters in the resulting string (assuming you need the padding because you are dealing with a fixed length).

But as I said you should really avoid doing this in your database.

Concatenation Breaks Numbers with Padded Zeros in SQL Server

I think this does what you want:

convert(varchar(6), a.[Fiscal Year] * 100 + a.[Fiscal Month])

Integer PadLeft function in T-SQL

I believe this may be what your looking for:

SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0') 

FROM tableA

or

SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]

FROM tableA

I haven't tested the syntax on the 2nd example. I'm not sure if that works 100% - it may require some tweaking - but it conveys the general idea of how to obtain your desired output.

EDIT

To address concerns listed in the comments...

@pkr298 - Yes STR does only work on numbers... The OP's field is an ID... hence number only.

@Desolator - Of course that won't work... the First parameter is 6 characters long. You can do something like:

SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA

this should theoretically move the goal posts... as the number gets bigger it should ALWAYS work.... regardless if its 1 or 123456789...

So if your max value is 123456... you would see 0000123456 and if your min value is 1 you would see 0000000001

T-SQL Remove Zero Padding as String Manipulation

DECLARE @Var VARCHAR(100) = '000000658410065446'

SELECT SUBSTRING(@Var, PATINDEX('%[^0]%',@Var), 100)

OR

SELECT SUBSTRING(@Var, PATINDEX('%[^0]%',@Var), 
LEN(@Var)- PATINDEX('%[^0]%',@Var)+ 1)

Both will return the same Result as follows

Result

658410065446


Related Topics



Leave a reply



Submit