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

SQL Server Create table to autoconvert int -> decimal

Sam
1#
Sam Published in 2017-11-14 20:48:17Z

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.

Sean Lange
2#
Sean Lange Reply to 2017-11-14 21:01:39Z

Why not just a basic query? You have to do the math in two steps because '00000010000' is large to fit into a basic numeric. But by multiplying by 1 it will implicitly convert to an int and then it is simple to divide by 100. Notice it needs be 100.0 so it will implicitly convert it to a numeric and not an int. Here are a couple example values.

select convert(numeric(9, 2), ('00000010000' * 1) / 100.0)
select convert(numeric(9, 2), ('00000010123' * 1) / 100.0)
shibormot
3#
shibormot Reply to 2017-11-14 21:48:44Z

Just a little helper stored procedure, to convert imported table to required format.

You specify imported table name, converted table name, which will be created and comma separated list of columns which are not required to be converted.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table source (
  id int not null identity(1,1),
  c1 varchar(100) not null,
  c2 varchar(100) not null,
  c3 varchar(100) not null,
  c4 varchar(100) not null,
  c5 varchar(100) not null,
  c6 varchar(100) not null
);

insert source (c1, c2, c3, c4, c5, c6) values
  ('a', '000001000', '000001000', '000001000', '000001000', 'b'),  
  ('c', '000002001', '000002002', '000002003', '200020002', 'd'),  
  ('e', '000003002', '000003002', '000003003', '300030003', 'f'),  
  ('g', '000004003', '000004002', '000004003', '400040004', 'h'),  
  ('i', '000005004', '000005002', '000005003', '500050005', 'j')
;

create procedure convert_table 
  @source varchar(max),
  @dest varchar(max),
  @exclude_cols varchar(max)
as
begin
  declare
    @sql varchar(max) = 'select ',
    @col_name varchar(max)

  if @exclude_cols not like ',%' set @exclude_cols = ',' + @exclude_cols
  if @exclude_cols not like '%,' set @exclude_cols = @exclude_cols + ','

  declare c cursor for
  select column_name 
  from information_schema.columns
  where table_name = @source

  open c
  fetch next from c into @col_name
  while @@fetch_status = 0
  begin
    if @exclude_cols like '%,' + @col_name + ',%'
      set @sql = @sql + @col_name + ','
    else
      set @sql = @sql + 'convert(numeric(11, 2), ' + @col_name + ') / 100 as ' + @col_name + ','

    fetch next from c into @col_name
  end
  close c
  deallocate c

  set @sql = substring(@sql, 1, len(@sql) -1)
  set @sql = @sql + ' into ' + @dest + ' from ' + @source

  --print(@sql)
  exec(@sql)
end
;

exec convert_table @source = 'source', @dest = 'dest', @exclude_cols = 'id,c1,c6'

Query 1:

select * from dest

Results:

| id | c1 |    c2 |    c3 |    c4 |         c5 | c6 |
|----|----|-------|-------|-------|------------|----|
|  1 |  a |    10 |    10 |    10 |         10 |  b |
|  2 |  c | 20.01 | 20.02 | 20.03 | 2000200.02 |  d |
|  3 |  e | 30.02 | 30.02 | 30.03 | 3000300.03 |  f |
|  4 |  g | 40.03 | 40.02 | 40.03 | 4000400.04 |  h |
|  5 |  i | 50.04 | 50.02 | 50.03 | 5000500.05 |  j |
Sam
4#
Sam Reply to 2017-11-14 21:50:03Z

So i didn't find the overly simple answer i was hoping for but i did find another way to accomplish my goal. If i set all columns that should have a decimal place to a decimal type I can then use the system tables and (Tsql?) to modify all columns of type decimal to equal itself/100.0.

DECLARE @tableName varchar(10)
SET @tableName = 'test21'

DECLARE @sql VARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + 'UPDATE ' + @tableName + ' SET ' + c.name + ' = ' + c.name + '/100.0 ;'
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE t.name = @tableName AND y.name IN ('decimal')

exec(@sql)

I have to dynamically construct the command within SQL based on information within the system tables and then execute it. .

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO