Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Character manipulation in sql query

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
rrhandle8
Asked:
rrhandle8
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Example:

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

returns
D0000123

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Update query:

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


mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
rrhandle8Author Commented:
Perfect!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now