Append information to data already in a column

Just recently I was thrown into the DBA roll... needless to say I'm not
a DBA. The customer has requested a query to take a column full of
numerical data (a 6 digit number) and run a query to append a .00
behind that number for every record in that column. Is this possible?
I've checked the online books and have done countless searches for a
hint of a query to no avail.
Any and all help would be greatly appreciated.
Thank you,
JIf you just want to represent the (integer?) number as decimal with '.00'
SELECT CAST([YourNumber] AS decimal(8,2)) AS "number"
FROM [YourTable]
If you actually want to change how the number is stored in the database:
ALTER TABLE [YourTable] ALTER COLUMN [YourNumber] decimal(8,2)
"JMiller" wrote:

> Just recently I was thrown into the DBA roll... needless to say I'm not
> a DBA. The customer has requested a query to take a column full of
> numerical data (a 6 digit number) and run a query to append a .00
> behind that number for every record in that column. Is this possible?
> I've checked the online books and have done countless searches for a
> hint of a query to no avail.
> Any and all help would be greatly appreciated.
> Thank you,
> J
>|||You have not told us the current data type of the column, and that
makes all the difference.
If it is an integer (bigint, int, smallint, tinyint) then the data
type would need to change to DECIMAL (or NUMERIC) with two decimal
places.
If it is already a decimal (or numeric) then the type has to change to
add two decimal places, being careful to keep sufficient length.
If the column is character, it needs to be lengthened by three
characters, and then updated to append the '.00' string after the data
that is already there.
If the column is varchar, it MIGHT need to be lengthened, and then it
would be updated as for char.
So, what is the column's data type?
Roy
On 15 Feb 2006 15:16:36 -0800, "JMiller" <Gloranard@.gmail.com> wrote:

>Just recently I was thrown into the DBA roll... needless to say I'm not
>a DBA. The customer has requested a query to take a column full of
>numerical data (a 6 digit number) and run a query to append a .00
>behind that number for every record in that column. Is this possible?
>I've checked the online books and have done countless searches for a
>hint of a query to no avail.
>Any and all help would be greatly appreciated.
>Thank you,
>J|||I apologize for forgetting to add that important piece of information.
The table is currently configured varchar (25).|||>I apologize for forgetting to add that important piece of information.
> The table is currently configured varchar (25).
update mytable set mycolumn = rtrim(mycolumn) + '.00'
where mycolumn is not null
Things to consider.
* The maximum size of any value in mycolumn is less than 23 (since you are
adding three additional characters). How to do this: select
max(len(mycolumn)) from mytable
* Verify that this is actually appropriate for the data found in the column
for all rows. In other words, are all values "properly" numeric.
* Perhaps the better approach is to change the datatype of the column.
Alternatively, create a computed column (or view) that does this for you.
Why? Well, the values that are currently in the table got there somehow.
It is likely that this functionality will continue to use the same format
when inserting or updating rows.
* You might need additional criteria in your where clause. You might not
want to change an empty string to '.00' (which is what the above query will
do).
* For testing purposes, you can wrap the above statement in a transaction.
If you rollback the transaction, you can issue the statement multiple times
and view the results. E.g.,
begin tran
update mytable set ...
select mycolumn from mytable
rollback tran|||Thanks you both for the help, it worked like a champ. Thank you, thank
you, thank you!