Today we will look something which is very quick and but quite frequently useful string operation over numeric datatype. This article is written based on a question asked by one of the users (name not disclosed as per request). Let us see how to show a fixed width number display.
Email was received as following:
Dear Pinal,
I have situation where I need to display my numbers in fixed format. Like
1 as 0000001
109 as 0000109
0987 as 0000987
Is there any string function in SQL Server which can do this?
There is no ready-made function in SQL Server which offers this solution but we can quickly write up something very simple. I have used the AdventureWorks database for our example purpose. I have noticed that some developers do not use CAST function but I strongly suggest to use it as if CAST function is not used and your column is of numeric datatype it will give errors.
USE AdventureWorks
GO
SELECT TOP 10 ContactID
FROM Person.Contact
GO
/* Method 1 Using RIGHT function*/
SELECT TOP 10 RIGHT('0000000'
+ CAST(ContactID AS VARCHAR(7)), 7) PaddedContactID
FROM Person.Contact
GO
/* Method 2 Using RIGHT AND REPLICATE function*/
SELECT TOP 10 RIGHT(REPLICATE('0', 7)
+ CAST(ContactID AS VARCHAR(7)), 7) PaddedContactID
FROM Person.Contact
GO
Following image demonstrates the result of the above two methods.
If you prefer you can create UDF for the same. I would encourage my readers to submit the UDF for satisfying the following criteria using the code provided above. UDF should take a parameter ‘Total Width of Number (in our example it is 7).
I will publish the reader who posts the first correct answer to this UDF on this blog with due credit.
Reference : Pinal Dave (https://blog.sqlauthority.com)
42 Comments. Leave new
Dear Pinal,
I have tried this query :
select right(‘000’ + cast(serial_no as varchar(3)),3) from MyTable
also this query:
select right(‘000’ + convert(varchar(3), serial_no),3) from MyTable
but it keep displaying results without leading zero.
I wonder what’s wrong, is there something in server side I should change?
Thanks
select right(‘1000’ + convert(varchar(3), no_Job_req),3) from coba
Dear Pinal,
I have tried this query :
select right(’000′ + cast(serial_no as varchar(3)),3) from MyTable
also this query:
select right(’000′ + convert(varchar(3), serial_no),3) from MyTable
but it keep displaying results without leading zero.
I wonder what’s wrong, is there something in server side I should change?
Thanks
update tablename
set
BOOK_NO = +’0’+BOOK_NO // add number of zero you want
BOOK_NO = +’00’+BOOK_NO // add number of zero you want
BOOK_NO = +’000’+BOOK_NO // add number of zero you want
BOOK_NO = +’0000’+BOOK_NO // add number of zero you want
BOOK_NO = +’00000’+BOOK_NO // add number of zero you want
BOOK_NO = +’000000’+BOOK_NO // add number of zero you want
BOOK_NO = +’0000000’+BOOK_NO // add number of zero you want
where LEN(BOOKINW.BOOK_NO) =6 // if you want to specify any condiions
Dear Saineshwar,
Thanks for your suggestion, it helps :)
I was focusing in one line command, meanwhile I should be able to do it in simple ways although I can’t do it in 1 line.
Best Regards,
i have tryed ur query but it is not working
Hi,
I am trying to get 15.6 to look like this: 0000015600000. Any thoughts on how I can pad both sides, without the decimal?
Thanks
Hi, I need some help with this script. I have the following in my connection string and it returns ‘201511’ instaed of ‘20150101’. What changes do I need to make to get the required.
(DT_STR,4,1252)YEAR( DATEADD( “dd”, -1, getdate() ))+(DT_STR,4,1252)MONTH( DATEADD( “dd”, -1, getdate() ))+(DT_STR,4,1252)DAY( DATEADD( “dd”, -1, getdate() ))+”.dmp”+”\\”+”comm_scheme”+”.txt”
that doesn’t look like T-SQL.
much simpler solutions available in SQL – use the ancient STR function – been there since SQL4.0
DECLARE @len SMALLINT = 16
DECLARE @value INT = 1234
SELECT REPLACE(STR(@value,@len),’ ‘,’0’)
— and the ‘0’ chould be any padding character
Thanks for sharing that Brent.
Questiom. I tried to use the right fnction on sql workbench, but it seems it doesn’t accept it…do you know why?
I need some assistance as I am new to T-SQL and still learn my way around I have a field value
6.2.1 HF 2
Which I need to strip the HF and add leading zeros to only where it’s a single digit number and then remove the ‘.’
So as an example I need it to look like 06020102
or 6.2.12 HF 2 it would then need to look like 06021202
Can anyone assist me in how I can accomplish this, thanks in advance
It is difficult to get the output in a simple straight forward query
( as i tried in my post
To get the required output, a UDF has been used
—————- UDF to parse string —————-
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value(‘(./text())[1]’, ‘varchar(max)’)))
From (Select x = Cast(”+ Replace(@String,@Delimiter,”)+” as xml).query(‘.’)) as A
Cross Apply x.nodes(‘x’) AS B(i)
);
—————- UDF to parse string —————-
Select A.*
,NewField = B.String
From Sample A
Cross Apply (
Select String = ltrim((Select cast(RetVal as varchar(25))
From (Select RetSeq,RetVal=Right(’00’+RetVal,2)
From [dbo].[udf-Str-Parse](replace(Field,’ ‘,’.’),’.’)
Where Try_Convert(int,RetVal)>=0 ) A
For XML Path (”)))
) B
Thank you Pinal Dave for always having such great tutorials. This site has helped me so many times.
No built in field yet in sql server 2016 which can do this on it’s own?