Specify the culture used within the "With Clause" of OpenJSON-CodePudding

I live in Denmark. Here the Thousand separator is a dot (.), and we use comma (,) as comma-separator.

I know that you can use TRY_PARSE to convert a varchar into a money/float value.

An example:

declare
    @JSON varchar(max)= 
    '
    {
        "Data Table":
        [
            {
                "Value" : "27.123,49"
            }
        ]
    }
    '

select  
    TRY_PARSE(Value as money using 'da-dk') "Correct Value"
FROM OpenJson(@json, '$."Data Table"')
WITH
(
    "Value" nvarchar(255)   N'$."Value"'
)

select  
    Value "Wrong Value"
FROM OpenJson(@json, '$."Data Table"')
WITH
(
    "Value" money   N'$."Value"'
)

This query gives me two results

My question is: Can I control the culture in the WiTH Clause of OpenJSON, so I get the correct result without having to use TRY_PARSE?

Target: SQL Server 2019

CodePudding user response：

Not directly in OPENJSON(), no. ECMA-404 JSON Data Interchange Syntax specifically defines the decimal point as the U 002E . character - and doesn't provide for cultural allowances - which is why you're having to define culture-specific values as strings in the first place.

The correct way to do it is only using TRY_PARSE or TRY_CONVERT. eg

select try_parse('27.123,49' as money using 'da-DK')