Convert data to numeric/decimal

• GrassHopper

  SSCarpal Tunnel

  Points: 4465

  More actions

  October 18, 2018 at 10:09 am

  #371723

  I have a varchar column that has amounts that are mixed ie
  42,180.00
  $123.44
  4,555.99
  £100,000
  $1,393.21

  I want to create a new column with those amounts above as a decimal/numeric type column.  How can i do this?

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  October 18, 2018 at 10:17 am

  #2010254

  GrassHopper - Thursday, October 18, 2018 10:09 AM

  I have a varchar column that has amounts that are mixed ie
  42,180.00
  $123.44
  4,555.99
  £100,000
  $1,393.21

  I want to create a new column with those amounts above as a decimal/numeric type column.  How can i do this?

  select CONVERT(decimal(10,2),REPLACE(REPLACE(REPLACE(T.C,'$',''),'£',''),',','')) myDecimalColumn, *
from (values ('42,180.00'),
('$123.44'),
('4,555.99'),
('£100,000'),
('$1,393.21')) T(C)

• Jeff Moden

  SSC Guru

  Points: 1003869

  More actions

  October 18, 2018 at 10:57 am

  #2010265

  Jonathan AC Roberts - Thursday, October 18, 2018 10:17 AM

  GrassHopper - Thursday, October 18, 2018 10:09 AM

  I have a varchar column that has amounts that are mixed ie
  42,180.00
  $123.44
  4,555.99
  £100,000
  $1,393.21

  I want to create a new column with those amounts above as a decimal/numeric type column.  How can i do this?

  select CONVERT(decimal(10,2),REPLACE(REPLACE(REPLACE(T.C,'$',''),'£',''),',','')) myDecimalColumn, *
from (values ('42,180.00'),
('$123.44'),
('4,555.99'),
('£100,000'),
('$1,393.21')) T(C)

  Haven't tried this particular set of data but it might be a little faster to simply convert them to the MONEY datatype and then to decimal.  The MONEY datatype isn't bothered by commas and valid currency markers.

  Of bigger concern might be the fact that once these are converted to decimal, the currency indications will be gone unless other measures are taken.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• GrassHopper

  SSCarpal Tunnel

  Points: 4465

  More actions

  October 18, 2018 at 11:06 am

  #2010269

  Jonathan AC Roberts - Thursday, October 18, 2018 10:17 AM

  GrassHopper - Thursday, October 18, 2018 10:09 AM

  I have a varchar column that has amounts that are mixed ie
  42,180.00
  $123.44
  4,555.99
  £100,000
  $1,393.21

  I want to create a new column with those amounts above as a decimal/numeric type column.  How can i do this?

  select CONVERT(decimal(10,2),REPLACE(REPLACE(REPLACE(T.C,'$',''),'£',''),',','')) myDecimalColumn, *
from (values ('42,180.00'),
('$123.44'),
('4,555.99'),
('£100,000'),
('$1,393.21')) T(C)

  This works great when all rows are populated with a value.  There are rows that have blank values  or just a "-" (negative sign).  How do i get around that?

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  October 18, 2018 at 11:21 am

  #2010274

  GrassHopper - Thursday, October 18, 2018 11:06 AM

  Jonathan AC Roberts - Thursday, October 18, 2018 10:17 AM

  GrassHopper - Thursday, October 18, 2018 10:09 AM

  I have a varchar column that has amounts that are mixed ie
  42,180.00
  $123.44
  4,555.99
  £100,000
  $1,393.21

  I want to create a new column with those amounts above as a decimal/numeric type column.  How can i do this?

  select CONVERT(decimal(10,2),REPLACE(REPLACE(REPLACE(T.C,'$',''),'£',''),',','')) myDecimalColumn, *
from (values ('42,180.00'),
('$123.44'),
('4,555.99'),
('£100,000'),
('$1,393.21')) T(C)

  This works great when all rows are populated with a value.  There are rows that have blank values  or just a "-" (negative sign).  How do i get around that?

  Use Jeff's solution:
  select convert(decimal(9,2),convert(money,T.C)) myDecimalColumn,*
from (values ('42,180.00'),
('$123.44'),
('4,555.99'),
('£100,000'),
('-'),
(''),
('$1,393.21')) T(C)