Solved

Character manipulation in sql query

Posted on 2011-09-18
4
335 Views
Last Modified: 2012-05-12
I don't know if this can be done or not.
I have a field in a query that always starts with a letter, and is followed by 1 to 7 numbers.  I need to insert 0's after the letter so the result is 7 characters long.

Examples:
D123  --> D000123
E01 --> E000001
0
Comment
Question by:rrhandle8
  • 3
4 Comments
 
LVL 75
ID: 36557157
Example:

Left("D123",1) & Format(Mid("D123",2),"0000000")

returns
D0000123

mx
0
 
LVL 75
ID: 36557160
Update query:

UPDATE Table1 SET Table1.FIELD1 = Left([FIELD1],1) & Format(Mid([FIELD1],2),"0000000");


mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36557170
OOPS ... 7 total


Left("D123",1) & Format(Mid("D123",2),"000000")

UPDATE Table1 SET Table1.FIELD1 = Left([FIELD1],1) & Format(Mid([FIELD1],2),"000000");
0
 

Author Comment

by:rrhandle8
ID: 36557194
Perfect!
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

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…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

735 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