Click here to Skip to main content
14,325,178 members
Rate this:
Please Sign up or sign in to vote.
I have a table named as Country as follows

admin1code
    1
    10
    11
    12
    5
    6
    45


I wants to update column data such that 0 should be appended at the beginning of number which are single unit EX. 1,2,3...9 etc becomes 01,02,03..09

so final column data after update should look like

admin1code
    01
    10
    11
    12
    05
    06
    45
Posted
Comments
digimanus 14-Mar-12 11:46am
   
what is the datatype for admin1code? (n)varchar ?
Rate this:
Please Sign up or sign in to vote.

Solution 1

assuming admin1code is of datatype (n)varchar
Update Country
set admin1code = right('00'+ admin1code, 2)
   
Comments
vikram_shinde 14-Mar-12 11:57am
   
many thanks...
OriginalGriff 14-Mar-12 11:58am
   
You might want a WHERE on that: what happens with 123, 124, etc.? :laugh:
digimanus 15-Mar-12 4:27am
   
:smile:
but I guess het want 01 then to be 001
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try:
UPDATE Country SET admin1code = '0' + admin1code WHERE LEN(admin1code) = 1 AND admin1code >= '0' and admin1code <='9'
   
Comments
gvprabu 28-Sep-12 5:59am
   
Nice :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100