October 30, 2019 at 5:16 pm
Hi:
Is there a function or some code to convert a number that's European (4.100,00 - dots in place of comma for thousands and comma for decimal point) into US (4,100.00)?
-278.999 should be -278,999
621.562,61 should be 621,562.61
Any help is appreciated.
Thanks !
October 30, 2019 at 5:39 pm
What is the datatype of the original data you want to convert?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2019 at 5:43 pm
This should not be a problem at the database tier in your tiered architecture. Do this in the input, before it even gets near the tables. You'll find ETL tools will be a great help in scrubbing your data.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 30, 2019 at 5:49 pm
It comes in file and then we have SSIS package to insert it into SQL table which has data type of varchar. Now when we want to use the Amount column we want to first convert it. If there is easy solution to convert it in SSIS package then I am also OK with that.
October 30, 2019 at 6:54 pm
This should not be a problem at the database tier in your tiered architecture. Do this in the input, before it even gets near the tables. You'll find ETL tools will be a great help in scrubbing your data.
This is a simple issue for the database tier which is where several different forms of ETL occurs.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2019 at 7:23 pm
If there is easy solution to convert it in SSIS package then I am also OK with that.
Yes there is. You need a Data Conversion transformation.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 30, 2019 at 7:59 pm
It comes in file and then we have SSIS package to insert it into SQL table which has data type of varchar. Now when we want to use the Amount column we want to first convert it. If there is easy solution to convert it in SSIS package then I am also OK with that.
If you have any control over it, rather change the data type in the table to decimal(18,2) to store the value correctly. Then you can do math in the data layer, and your presentation layer can format it in any way that it needs.
November 4, 2019 at 1:03 pm
This should not be a problem at the database tier in your tiered architecture. Do this in the input, before it even gets near the tables. You'll find ETL tools will be a great help in scrubbing your data.
On a standalone application I'd be 100% behind this - handling of locale specific I/O belongs in the presentation layer.
However, the OP has indicated this is a datafile supplied externally to the application - probably external to the company where there is no control over its format available. Here the presentation layer dealing with the input is in the foreign application.
Communication between applications is commonplace and the interface between then may be at any layer - in this case its being performed at the data layer - the best place to handle this is the interface between the data layers of the two applications which will generally be implemented on the database engine.
If you just need to perform the conversion then this can be handled in the ETL tools - e.g. SSIS or a SP written to do so and those will constitute your interface.
If you need to perform additional validation (this will depend on how clean you expect the datafile to be) of that input you probably want to set up either a staging database or a set of staging tables as your interface and perform your conversion and validation there before importing to your application.
You could consider setting the collation for the relevant columns in those staging tables/database to reflect the origin data - then when you convert to the appropriate numeric datatype it will be handled automatically
I'd be tempted to set it up as a staging DB with that DB configured using French collations and country settings, Import your datafile into that.
Add a new column with an appropriate numeric data type - possibly as a computed column and populate this with the type converted value of your text column.
You then after completing any other required validation import the data to your application DB but use the new numeric column in place of the VARCHAR one
Rereading the OPs question the data has the triplet separators - you would need to strip those out first using REPLACE to replace '.' with ''. This would allow the above staging DB to handle it, if you left the staging DB with US settings then you would need a second REPLACE to change ',' to '.' before converting to numeric - You could build that into the SSIS if you wanted to skip the staging DB but I'd only want to skip that if I was confident the datafile would always be clean.
November 5, 2019 at 7:18 pm
hmmm... I would probably use a Derived Column transformation:
(DT_NUMERIC,20,2)(REPLACE(REPLACE([EuropeanVarcharAmount],".",""),",","."))
or something like that...
November 5, 2019 at 9:03 pm
This isn't pretty, but it seems to work.
The code assumes that '^' is a safe character to use to bookmark the position of the ending period. If not, pick another. Once the period/decimal point position is bookmarked, the code replaces the periods with commas and replaces the bookmark with a period.
declare @value varchar(40) = '23.621.562,61' -- should be 23,621,562.61
select replace(replace(stuff(@Value,len(@Value) - charindex(',',reverse(@Value))+1,1,'^'),'.',','),'^','.')
If you are using SSIS, this logic could be better implemented in the SSIS process to keep the CPU load off the SQL Server to a minimum. But if it absolutely has to be done in SQL, this should work.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply