Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Character manipulation in sql query

Posted on 2011-09-18
4
Medium Priority
?
340 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
[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
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

604 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