how to convert the decimal value into number in sql server

Naresh y 146 Reputation points
2023-05-08T12:39:10.58+00:00

Hi team.

i have a table where i can convert to decimal to number. and to restrict the length to 5 places

Source values

12.09870

156.98655

23.099878

Desired list

120987

15698

23099

I am trying to load this data into notepad file, the length is not restricting to 5 places while generating the file

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,696 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 55,601 Reputation points
    2023-05-08T16:20:17.5566667+00:00

    select left(replace(cast(mycolumn as varchar(20)),'.','') + '00000', 5) as n
    from mytable

    0 comments
  2. ZoeHui-MSFT 32,736 Reputation points
    2023-05-09T02:35:36.31+00:00

    Hi @Naresh y,

    You may add Derived Column with below expression to do that.

    LEFT(((DT_WSTR,50)(yourcolumn * 10000)),5)

    User's image

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.