insert value comma/ dot issue

Question

insert value comma/ dot issue

kj700098 39683

Old Hand

Points: 384
More actions
October 31, 2016 at 6:29 pm

#315001

hi
I have a problem with insert statement because where I live comma is a dot
database collation = Danish_Norwegian_CI_AS
INSERT INTO dbo.TestTable
VALUES (1, '1 Value', 18.2),
(2, '2 Value', 19.2),
(3, '3 Value', 20.2),
(4, '4 Value', 21.2)
GO
SELECT * FROM dbo.TestTable
GO
If I type VALUES (1, '1 Value', 18,2) I got an error, because I have used comma which is normal in most countries in europe.
So how can I convert or replace the dot to a comma ?????

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

I'm not totally sure but I believe you'll have to change the language setting. You'd do that with the follow syntax example from Books Online. Make the appropriate substitution.

SET LANGUAGE { [ N ] 'language' | @language_var }

You can find out what the appropriate substitution might be by running the following code, which is also from Books Online.

SELECT *

FROM sys.syslanguages

;

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

kj700098 39683 Old Hand Points: 384 More actions · Answer 2

thanks but I have tried default language = norwegian and no help.

The issue is that that the transact sql see four columns here

VALUES (1, '1 Value', 18,2)

here the sql see three columns as the true

VALUES (1, '1 Value', 18.2)

the danish- Norwegian collation make the 18.2 --> 18,2 in the table column.

but what I want is to type 18,2 in the insert statement.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

kj700098 39683 (10/31/2016)
thanks but I have tried default language = norwegian and no help.
The issue is that that the transact sql see four columns here
VALUES (1, '1 Value', 18,2)
here the sql see three columns as the true
VALUES (1, '1 Value', 18.2)
the danish- Norwegian collation make the 18.2 --> 18,2 in the table column.
but what I want is to type 18,2 in the insert statement.

Yep... picked up the nature of the issue the first time. Thought the language thing might be a help if you encapsulated the 18,2 in parenthesis but no joy even there. I don't know the answer to this but at least this post will serve as a "bump" to your problem. Hopefully someone from your side of the pond will pick up on it.

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

DesNorton SSC-Insane Points: 24387 More actions · Answer 4

Are you able to get the source data as quoted values

SELECT ID, txt, REPLACE(val, ',', '.') AS val

FROM (

VALUES (1, '1 Value', '18,2'),

(2, '2 Value', '19,2'),

(3, '3 Value', '20,2'),

(4, '4 Value', '21,2')

) t(ID, txt, val)

How to post data/code on a forum to get the best help.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 5

kj700098 39683 (10/31/2016)
hi
<snip>...
If I type VALUES (1, '1 Value', 18,2) I got an error, because I have used comma which is normal in most countries in europe.
So how can I convert or replace the dot to a comma ?????

The difference in notation for both decimals and dates is always a hard part. The times I ran into it I handled the issue in the application/front-end. I captured the input in the application and modify it to the US notification before sending it to the database. I don't know of any way to send European style decimals to the database (except as string but you absolutely don't want to go there).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

DesNorton SSC-Insane Points: 24387 More actions · Answer 6

Since this is SQL 2012, you could also use TRY_PARSE

SELECT

TRY_PARSE('13.2' AS DECIMAL(18,2) USING 'en-US')

, TRY_PARSE('13,2' AS DECIMAL(18,2) USING 'de-DE')

How to post data/code on a forum to get the best help.