Posted on 2007-08-03
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 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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 92

    Accepted Solution

    SQL Server:

    Right('000000' + TheColumn, 6)


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

    Expert Comment

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now