Share via

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.
14,369 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,632 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 69,976 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 40,051 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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.