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
SQL Query to Search for Room Availability
How to Use a SQL Update Statement to Add 1 Year to a Datetime Column
Sql:Using Group by and Max on Multiple Columns
"Ora-01438: Value Larger Than Specified Precision Allowed for This Column" When Inserting 3
How to Generate All Possible Data Combinations in SQL
How Is This Script Updating Table When Using Left Joins
Ms Access Select Top N Query Grouped by Multiple Fields
Global Temporary Tables in SQL Server
How to Merge the Columns from Two Tables into One Output
SQL Coalesce with Empty String
How to List Field's Name in Table in Access Using SQL
Add Emoji/Emoticon to SQL Server Table
Update Values in Struct Arrays in Bigquery