3

Using Microsoft SQL Server 2022 I have the following error when trying to convert a negative numeric value to a NVARCHAR(MAX)

com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Arithmetic overflow error converting expression to data type nvarchar.

This can be reproduced with the following code:

CREATE TABLE "MY_DB"."MY_SCHEMA"."TEST"
(
    [VALUE] FLOAT
);

INSERT INTO "MY_DB"."MY_SCHEMA"."TEST"
VALUES (-0.0123456789);

SELECT CONVERT(NVARCHAR(MAX), "VALUE" , 3) 
FROM "MY_DB"."MY_SCHEMA"."TEST"

Strangely this works with non negative values or with NVARCHAR(4000) but I need NVARCHAR(MAX) because I then concatenate these values into a long string with STRING_AGG.

Any solution to this issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fabich
  • 2,768
  • 3
  • 30
  • 44
  • I have also asked this question to Microsoft support here: https://feedback.azure.com/d365community/idea/62a6aad7-3f3c-ee11-a81c-0022484c92b1 – Fabich Aug 16 '23 at 14:19
  • 2
    That is quite strange. Not quite I understand why you have to convert this to nvarchar(max). You can pass a shorter nvarchar to string_agg and still get back an nvarchar(max). But you can at least work around this by converting this with this. `convert(nvarchar(max), CONVERT(NVARCHAR(4000), [VALUE], 3))`. – Sean Lange Aug 16 '23 at 16:05

1 Answers1

2

There a couple of options open to you here. One would be to CONVERT the value to a non-MAX length first and then a MAX length. Alternatively you could concatenate a MAX length zero length string in your aggregation, which would implicitly cast it to a MAX length (oddly, this is the method I've used historically when needed MAX length aggregation on non-MAX length data):

CREATE TABLE dbo.Test (Value float);


INSERT INTO dbo.Test
SELECT V.f
FROM (VALUES (0.0123456789),
             (1.0123456789),
             (-0.0123456789),
             (-1.0123456789))V(f)
     CROSS JOIN GENERATE_SERIES(1,2000) GS;
GO

SELECT STRING_AGG(CONVERT(nvarchar(MAX),CONVERT(nvarchar(24),T.Value,3)),N','),
       STRING_AGG(CONVERT(nvarchar(MAX),N'') + CONVERT(nvarchar(24),T.Value,3),N',')
FROM dbo.Test T;
GO

DROP TABLE dbo.Test;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • The double conversion seems to work ! Thanks for your answer ! Do you know if it is a known bug in SQL server ? – Fabich Aug 16 '23 at 16:22
  • Not off the top of my head, no. – Thom A Aug 16 '23 at 16:50
  • Interestingly enough it doesn't manifest on 2014. I'm surprised this has never been picked on before, it seems to happen with any negative float with style 3 https://dbfiddle.uk/YTAtlmDf – Charlieface Aug 16 '23 at 17:21
  • 2
    It's apparently been reported by @PaulWhite on Azure Feedback already https://feedback.azure.com/d365community/idea/bbc28ac6-f66e-ed11-a81b-000d3adb7ffd – Charlieface Aug 16 '23 at 18:36