add additional string to a column value in sql RRS feed

  • Question


    i have a table that have a column called Custmerid with data type char......it has values like = 7788 and i want to a character to the column so that cusid would have a five digite value like 07788.....using sql ..need help!
    Thursday, September 18, 2008 7:24 PM

All replies

  • You should do this ahead of writing the data in the column in your application or stored procedure code as:


    Code Snippet

    declare @charvalue char(10)

    set @charvalue = '12345'

    select @charvalue = right(replicate('0', 10)+rtrim(ltrim(@charvalue)), 10)

    print @charvalue



    You absolutely should not use a trigger to do this after insert/update.  If you are using .NET look at the padright/padleft functions of a string.


    However, this is a display kind of thing, and you shouldn't really be doing it with your database data.  If this is a numeric data column, then you should be storing the data as a int/bigint/numeric datatype in SQL Server, and using the application code to control display of the additonal numbers.  You are using excess dataspace and performance of an int versus a char datatype on joins is much better.

    Thursday, September 18, 2008 9:36 PM