Format select statement to pad text with leading zeros

Posted on 2010-11-23
Last Modified: 2012-05-10

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

Question by:SASnewbie
  • 3
  • 3
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34197302
something like

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

Author Comment

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
LVL 13

Accepted Solution

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

select RIGHT ('0000000000'+field1,10 ) from table
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


Author Comment

ID: 34197373
Hi andycrofts,

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


Author Comment

ID: 34197518
Thanks! That works perfectly!
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34197535

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
execute a MS SQL script as a schedule SQL job 72 101
Dynamics crm 2011 8 46
SQL Server memory Issue 7 76
Filtered index 5 4
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now