how to convert the decimal value into number in sql server

Question

Naresh y 146

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

Wilko van de Velde 2,241 Reputation points

2023-05-08T12:56:18.39+00:00

Can you explain how you export the data?
Naresh y 146 Reputation points

2023-05-08T14:14:00.84+00:00

i am exporting the data using ssis package
Wilko van de Velde 2,241 Reputation points

2023-05-08T14:27:01.78+00:00

Make sure that you import and export data with the correct data type. To check this see chapter "Changing Data type from Advanced Editor" on https://www.sqlshack.com/ssis-data-types-change-from-the-advanced-editor-vs-data-conversion-transformations/
Naresh y 146 Reputation points

2023-05-08T15:59:10.61+00:00

i have already tried this technqie ,provide any logic in sql so that i can use that logic to update my source query

2 answers

Wilko van de Velde 2,241 Reputation points

2023-05-08T12:56:18.39+00:00

Can you explain how you export the data?
Naresh y 146 Reputation points

2023-05-08T14:14:00.84+00:00

i am exporting the data using ssis package
Wilko van de Velde 2,241 Reputation points

2023-05-08T14:27:01.78+00:00

Make sure that you import and export data with the correct data type. To check this see chapter "Changing Data type from Advanced Editor" on https://www.sqlshack.com/ssis-data-types-change-from-the-advanced-editor-vs-data-conversion-transformations/
Naresh y 146 Reputation points

2023-05-08T15:59:10.61+00:00

i have already tried this technqie ,provide any logic in sql so that i can use that logic to update my source query

Answer 1

Bruce (SqlWork.com) 82,146 Volunteer Moderator

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

Answer 2

ZoeHui-MSFT 41,536

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.