[RESOLVED] How to cast string (with comma or/and decimal) to integer/decimal?

Question

[RESOLVED] How to cast string (with comma or/and decimal) to integer/decimal?

PasLe Choix

Say Hey Kid

Points: 665
More actions
August 31, 2018 at 12:32 pm

#408327

my original raw csv file contains column of "integer" but like String:
ID, Account, Amount 1,234,"56,789" 2,345,"6,789.01"
This is actually common in English denoting numbering with comma, however, because this is a csv file, meaning a comma could introduce that column be split into two columns, hence there is a double quote pair around it.
How could I make the right cast to convert those values into decimal or integer correctly?
Can anyone help? Thank you very much

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

select convert(decimal(10,2), replace(replace('"6,789.01"', '"', ''), ',',''))
would do the trick - but if using SSIS or other tools those sometimes handle that on their own without any particular coding.

Thom A SSC Guru Points: 99130 More actions · Answer 2

If you're using an ETL tool, then simply specifying your text qualifier as " with handle the quotations (and cause the ETL tool to ignore them during the import". Frederico is right as well, many ETL tools will be happy to implicitly convert a value like 123,456,789.123 to 123456789.123. If it isn't, however, then the easiest way would be to import the value as text and use a transformation to remove the commas (REPLACE is a very common function, with the same usage across many languages), and then cast/convert it to a decimal.

I don't think you need to do it, but if you were using SSIS, and needed to both remove the commas and convert it, the syntax would be something like:
(DT_NUMERIC,12,3)(REPLACE([Amount],",",""))

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 3

frederico_fonseca - Friday, August 31, 2018 12:36 PM
select convert(decimal(10,2), replace(replace('"6,789.01"', '"', ''), ',',''))
would do the trick - but if using SSIS or other tools those sometimes handle that on their own without any particular coding.

Thank you very much for your quick help.

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 4

Thom A - Friday, August 31, 2018 12:44 PM
If you're using an ETL tool, then simply specifying your text qualifier as " with handle the quotations (and cause the ETL tool to ignore them during the import". Frederico is right as well, many ETL tools will be happy to implicitly convert a value like 123,456,789.123 to 123456789.123. If it isn't, however, then the easiest way would be to import the value as text and use a transformation to remove the commas (REPLACE is a very common function, with the same usage across many languages), and then cast/convert it to a decimal.
I don't think you need to do it, but if you were using SSIS, and needed to both remove the commas and convert it, the syntax would be something like:
(DT_NUMERIC,12,3)(REPLACE([Amount],",",""))

Thank you, indeed this is not used in SSIS.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Instead of using expensive REPLACEs, you could always convert it to the MONEY datatype and then to DECIMAL. The MONEY datatype takes commas in stride.

--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)

ssavva05 Grasshopper Points: 12 More actions · Answer 6

In my case, the replace with currency convert and then decimal convert works as expected! Thanks for the tip for the money(currency) field conversion, and then to decimal!

This reply was modified 2 years, 5 months ago by ssavva05.
This reply was modified 2 years, 5 months ago by ssavva05.