Home SQL Server Create table to autoconvert int -> decimal
Reply: 0

SQL Server Create table to autoconvert int -> decimal

user8457
1#
user8457 Published in September 19, 2018, 6:42 pm

I'm not sure if this is possible but here goes.

I have financial data stored in a csv format. The data unfortunately lacks any decimal points in the dollar fields. so $100.00 is stored as '00000010000'. Its also stored as a string. In my current setup I upload the csv file into a staging table with all columns set to varchar(x).

I know that if I try to insert this value into an integer column it will automatically convert it to 10000 of type integer, but that means I am missing my decimal place.

Is there anyway I can create a table such that inserting an integer stored as a string or integer automatically converts it to an decimal with 2 places behind the decimal????
EX: '000010000' -> 100.00

I know I can cast the column to a decimal and divide the existing value by 100.... but this table has 100+ columns with 60+ of them needing to be recast. This is also only table 1 of 6. I want to avoid creating commands to individually changing the relevant columns. Not all columns containing a number need the decimal treatment.

share|improve this question
  • which version of sql-server? – maSTAShuFu Nov 14 '17 at 20:49
  • microsft sql server management studio 2016 – Sam Nov 14 '17 at 20:50
  • 1
    If you use STUFF to put a . in the string in the correct location then the SQL conversion should handle converting it to a decimal or money more easily without having to do the division. – Evan Steinbrenner Nov 14 '17 at 20:51
  • if you're sure the last two decimal places are always zero, you can ignore them. So instead of your string with the last two decimal places representing the pennies, you use a string representing the whole number of dollars. – Beth Nov 14 '17 at 21:00
  • They are rarely 0, that was only for the example. – Sam Nov 14 '17 at 21:01

3 Answers 3

active oldest votes
up vote 0 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.292649 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO