Posted on 2007-08-03
Medium Priority
Last Modified: 2013-11-05
I am developing an Access application using Access as the front end and SQL Server as the back end database. If you have an Access table with a field defined as nvarchar 6 and the table is populated with some fields that are missing the leading zero, how would you perform a SQL update to insert a leading zero into the values that only show the trailing 5 positions.

For ex:
a value appears as "23456" and should appear as "023456"
Question by:zimmer9
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19628033
leading 0 is a display issue, not a data issue.
depending on where exactly the sql runs on (sql server or access), you can put a formula.
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 19628087
SQL Server:

Right('000000' + TheColumn, 6)


Right("000000" & TheColumn, 6)
LVL 44

Expert Comment

ID: 19628539
That will actually modify and then take the right six characters of that field in every record.  Would this be any faster, or would the system actually replace every instance with itself for lengths <> 5?

myFld=IIf(len(myFld)=5, "0" & myFld),"myFld")

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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