[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Format select statement to pad text with leading zeros

Posted on 2010-11-23
6
Medium Priority
?
702 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 total points
ID: 34197351
concat is MySQL not MS sql

select RIGHT ('0000000000'+field1,10 ) from table
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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