Adding additional numbers to a column

  • 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

  • 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

  • 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

  • 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?

  • 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