5 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.


Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num VARCHAR(32) = '3,6';
SELECT CONVERT(DECIMAL(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained@Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.


Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num VARCHAR(32) = '3,6';
SELECT CONVERT(DECIMAL(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.


Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num VARCHAR(32) = '3,6';
SELECT CONVERT(DECIMAL(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.

4 added 154 characters in body
source | link

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.


Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

The PARSE() documentation has the list of valid cultures.


If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work; you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

The PARSE() documentation has the list of valid cultures.


If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work; you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.


Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

The PARSE() documentation has the list of valid cultures.

3 added 857 characters in body
source | link

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num VARCHAR(32) = '3,6';
SELECT CONVERT(DECIMAL(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.


If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work; you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num VARCHAR(32) = '3,6';
SELECT CONVERT(DECIMAL(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num VARCHAR(32) = '3,6';
SELECT CONVERT(DECIMAL(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.


If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost VARCHAR(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(DECIMAL(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work; you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.

2 added 361 characters in body
source | link
1
source | link