SQL Server Combine Multiple CAST?

sigwle7e 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(118)

I have a file that has everything as nvarchar, including all the numbers, and there are no decimals, so '12.35' would just be stored as '1235'. Then I need it output with 4 decimal places.

I can't just divide and cast to decimal. It's ugly but this works.

SELECT CAST(CAST(rso.[NET_PRICE]    AS Decimal(20,4)) / 10)    AS Decimal(20,4) AS [Net Price]
SELECT CAST(CAST(rso.[Unit_Price]   AS Decimal(20,4)) / 10000) AS Decimal(20,4) AS [Unit Price]

Is there a way to combine the CAST or something to make it more efficient? I have about 20 million records, and each record has about 40 of these, so they are eating up cycles.

sql-server

来源：https://stackoverflow.com/questions/77232973/combine-multiple-cast

1条答案

按热度按时间

swvgeqrz1#

Short answer: No, there is not a better option.

Long answer: It depends what shall be the outcome and implicit versus explicit.

It all comes down what source data can be implicitly converted to "correct" values, even if the intermittent implicit conversions choose the most "appropriate" type. Which might not work for certain outlying values.

Simple test data and outputs shown:

DECLARE
  @Rows
  TABLE
(
  SomeColumn    nvarchar(12)
)
;
INSERT INTO
  @Rows
VALUES
  ( N'123456' )
, ( N'4201' )
, ( N'123456789012' ) -- 1234567890123 yields truncation error
, ( N'-12345678901' ) -- -123456789012 yields truncation error
, ( N'NaN' )
;

SELECT
  SomeColumn AS OriginalString
, TRY_CAST( SomeColumn AS decimal(20,4) ) AS DecimalValue
, TRY_CAST( SomeColumn AS decimal(20,4) ) / 100 AS CalculatedSimpleValue
, CONVERT( decimal(20,4), TRY_CONVERT( decimal(20,5), SomeColumn ) / 100 ) AS CalculatedDoubleValue
FROM
  @Rows
;

Result is:

OriginalString  DecimalValue        CalculatedSimpleValue   CalculatedDoubleValue
123456          123456.0000         1234.56000000           1234.5600
4201            4201.0000           42.01000000             42.0100
123456789012    123456789012.0000   1234567890.12000000     1234567890.1200
-12345678901    -12345678901.0000   -123456789.01000000     -123456789.0100

The third column shows where a calculation after the cast converted the type to something else than decimal 20 with 4 decimals.

However due to this structure of conversion code generation (in Excel) is really fast and I like Excel for function-copy-column-down for these tasks.

Final technical note: Use TRY_CONVERT or TRY_CAST when dealing with character columns as input to not fail in the middle of 20 million rows conversion work.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Combine Multiple CAST?

1条答案

相关问题

热门标签

最新问答