Remove decimal point from all values

HYT 0 Reputation points
2023-01-24T14:25:12.5533333+00:00

I have imported a table into synapse. The issue is that the column values have a decimal point which i want to remove.

Current value:
1.345698
0.046378
10.85364

How i want it:
1345698
46378
1085364

Whats the best approach of doing it?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,120 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Nandan Hegde 29,471 Reputation points MVP
    2023-01-25T04:22:33.5266667+00:00

    Hey,

    assuming this is a 1 time activity, you can use a tsql query to update

     

    create table #test
    (
    c1 decimal(10,3)
    )

     

    insert into #test
    Select 1.235 union
    select 0.003 union
    select 0.234

     

     

    Update #test
    set c1=replace(c1,'.','')

     

    Select * from #test

    output :

    User's image

    If you dont need 000 at the end, you need to update the datatype of column to varchar/int

    0 comments No comments