CODEWITHSUNDEEP
sqlsql-server
Rajas
Rajas

Reputation: 3

UPDATE the column value in table depending on current value of the column

I have a table called Customers and it has a column called Balance. If the balance of the customer ends with '00', say 3000 then it should be updated to 30.00 in other cases like 1333 it should not change the value.

I have been able to differ the values like 3000 from 1333 by using 

SELECT Balance
from dbo.Customers
where SUBSTRING(REVERSE(Balance),1,2) = '00'

but cannot find anywhere how to convert those found values like 3000, 6000, 9000 to 30.00, 60.00 and 90.00

EDIT 1 while updating it is important to me to have the '.00' like 30.00 , doing 30, 60, 90 is NOT an option And the datatype of the Balance column is NVARCHAR(MAX)

Upvotes: 0

Views: 79

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13026

here's your update script.

Update Customers set Balance =  
cast(cast(cast(Balance as decimal)/100.00 as numeric(36,2)) as nvarchar(max)) 
Where (cast(Balance as decimal)%10.00) = 0

Upvotes: 0

Squirrel
Squirrel

Reputation: 24803

right(Balance, 2) = '00' to check for required condition

perform the division Balance / 100 then convert to decimal(20,2) and then again convert to varchar

select  Balance,
        case    when    right(Balance, 2) = '00'
        then    convert(varchar(20), convert(decimal(20,2), Balance / 100))
        else    Balance
        end
from    Customer

Upvotes: 1

Related Questions