Post reply

Adding additional numbers to a column

  • January 18, 2007 at 10:24 am

    #368859

    I have a table with a column that has 6 digit numbers. i have changed that column to be varchar(8) now and want to know how to automatically put a '00' at the end of each of these, so that when new 6 digit numbers are added, a '00' is automatically added too.

     

    Thanks

    noob

  • January 18, 2007 at 10:36 am

    #684065

    Well you need to write a On Insert trigger for that if you want it to be automatically get appended with '00' as suffix.

    Prasad Bhogadi
    www.inforaise.com

  • January 18, 2007 at 10:37 am

    #684066

    Better way is to format it prior to sending to the table from your frontend code or inside the query.

    Prasad Bhogadi
    www.inforaise.com

  • January 18, 2007 at 10:42 am

    #684067

    So if i am creating a new table and want the '00' to be added to the data comming into it. what would the code look like?

  • January 18, 2007 at 11:23 am

    #684070

    Is this what you are looking for?

    insert into myTable (myColumn) values (@myValue + replicate('0', 8-datalength(@myValue)));

    you could update the existing values in a similar fashion:

    update myTable

    set myColumn = myColumn + replicate('0', 8-datalength(myColumn))

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply