Solved

Format select statement to pad text with leading zeros

Posted on 2010-11-23
6
680 Views
Last Modified: 2012-05-10
Hello,

What is the code used to format a field selected so that it always has a fixed number of characters (i.e., padded with leading 0s for a total length of 10)?

Example for Field1:

Actual                   Desired
123                        0000000123

Thanks,
SASnewbie
0
Comment
Question by:SASnewbie
  • 3
  • 3
6 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34197302
something like

select RIGHT ('0000000000'+field,10 ) from table
0
 

Author Comment

by:SASnewbie
ID: 34197304
Here is a sample of my select statement that I have tried:

SELECT    Client, right(concat("0000000000", Field1),10) as Loan_num,  Result
0
 
LVL 13

Accepted Solution

by:
Philip Pinnell earned 250 total points
ID: 34197351
concat is MySQL not MS sql

select RIGHT ('0000000000'+field1,10 ) from table
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:SASnewbie
ID: 34197373
Hi andycrofts,

I'll try that and get right back to you,

Thanks!
0
 

Author Comment

by:SASnewbie
ID: 34197518
Thanks! That works perfectly!
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34197535
Thanks
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question