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.
13,055 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,485 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 59,036 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 No comments

  2. ZoeHui-MSFT 34,576 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.