You must Sign In to post a response.
  • Category: SQL Server

    Convert varchar to int

    Hi ,
    I have a table column which is varchar(3000), values are stored like 840.00
    I want to divide this column value by 12 and need to store in same column as varchar.
    (should be 70 for 840.00). please let me know this conversion.

  • #762801
    Hello Sankar,

    Refer the below code :

    DECLARE @val INT;
    DECLARE @cols INT;
    DECLARE @amount DECIMAL(18,2);
    DECLARE @id INT;
    SET @val = 1;

    --Counting number of rows exists in table
    SET @cols = (SELECT COUNT(*) FROM Product)

    --Retrieving amount column. Just for view purpose
    SELECT Amount FROM Product

    --Looping till number of rows exists in table. To get values which is required.
    WHILE @val <= @cols

    --Retrieving id and amount row by row
    SELECT TOP (@val) @id = ProdID, @amount = Amount FROM Product
    SET @amount = @amount / 12;

    --Updating Amount column with new value against id
    UPDATE PRODUCT SET Amount = @amount WHERE ProdID = @id
    SET @val = @val + 1;

    ----Retrieving amount column. Just for view purpose after updation done.
    SELECT Amount FROM Product

    See the attached image. So that you will understand the output i have got which is right or not.

    Hope this is what you are looking for.

    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

    Delete Attachment

  • #762825
    Hai Sankar,
    First of all, you need to convert the value to the Decimal. Once the value is Decimal, then you can divide it with 12 and get the result also as decimal. Finally convert this value to varchar and store in to the same table column.

    SELECT CAST(Column1 AS decimal(4,2)))/12 as result FROM table

    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #763299
    Hello Sankar,

    Please try with the below query :-

    Update TableName set Column = convert(varchar(500), convert(decimal(20,2)Column)

    TableName - provide your table name
    Column - provide your column name

    Above query help you a lot.


  • Sign In to post your comments