TRY_PARSE: Make the Culture Explicit for Numeric Text

TRY_PARSE needs an explicit culture when punctuation has a specific numeric meaning. I keep that culture beside the source text. A successful conversion alone cannot establish that the intended number was read.

Different sources can use a comma or a period as a decimal separator. The other character may mark digit grouping. Treating these conventions as interchangeable can change a value rather than merely change its display.

Keep the text and its culture together

The query includes an en-US representation and a de-DE representation of the same amount. It also includes plain numeric text, invalid text and NULL. Each row carries its intended culture explicitly.

The target type is decimal with precision ten and scale two. The input column is Unicode text. The query returns the parsed value beside the complete input and culture name.

TRY_PARSE is used only for string-to-number parsing in this example. The batch creates no objects and changes no session language. A case identifier provides an explicit result order for all five rows.

WITH Inputs AS
(
    SELECT CaseId, InputText, CultureName
    FROM (VALUES
        (1, CAST(N'1,234.50' AS nvarchar(40)), CAST(N'en-US' AS nvarchar(10))),
        (2, N'1.234,50', N'de-DE'),
        (3, N'1234.50', N'en-US'),
        (4, N'not a number', N'en-US'),
        (5, NULL, N'en-US')
    ) AS v(CaseId, InputText, CultureName)
)
SELECT CaseId, InputText, CultureName,
       TRY_PARSE(InputText AS decimal(10,2) USING CultureName) AS ParsedNumber,
       CASE WHEN InputText IS NULL THEN 'Missing input'
            WHEN TRY_PARSE(InputText AS decimal(10,2) USING CultureName) IS NULL THEN 'Invalid numeric text'
            ELSE 'Parsed' END AS InputStatus
FROM Inputs
ORDER BY CaseId;

Read the equivalent numeric inputs

The en-US text 1,234.50 should parse as 1234.50. The de-DE text 1.234,50 should produce the same numeric value. Their punctuation differs, but their accompanying cultures explain that difference.

The plain en-US text 1234.50 should also produce that value. These first three rows are deliberately equivalent inputs. Comparing them establishes this small example’s parsing contract rather than a universal punctuation replacement rule.

The invalid text should return NULL with an Invalid numeric text status. The missing source should also return NULL, but with a Missing input status. A nullable numeric result cannot distinguish those cases on its own.

The diagnostic checks the original text for missing input before checking the parsed result. That preserves the reason for a missing numeric value. Applications can then decide whether to request a correction or accept an intentionally absent value.

Culture is part of the input contract

If culture is omitted, TRY_PARSE uses the current session language. That adds an environmental assumption to the conversion. I prefer explicit cultures when a file or upstream system has a known formatting convention.

The example uses two known, valid culture names. An invalid culture name can raise an error rather than behave like invalid numeric text. TRY_PARSE should not be described as a promise that every invalid argument returns NULL.

Do not infer a culture from punctuation alone when several formats are possible. Obtain the source convention from the import contract. Ambiguous text can be valid under more than one convention with different meanings.

Removing all punctuation before parsing is a different transformation. It can discard a decimal separator that mattered. Preserve the original text so any interpretation can be reviewed and corrected.

Choose a parsing tool for the actual requirement

TRY_PARSE supports string parsing into numeric and date-time types. It is not a general replacement for CAST or CONVERT. Straightforward SQL type conversions may need those tools instead.

TRY_PARSE relies on the .NET common language runtime and has parsing overhead. This example makes no timing claim and performs only bounded conversions. A large import needs its own throughput and deployment checks.

The target decimal type also has limits. A successfully understood text value can still fail to fit the requested precision or scale. Define the numeric range and required fractional digits before accepting an imported value.

Keep invalid rows visible rather than silently substituting zero. Zero is a valid amount with a different meaning from a failed parse. The source, culture, result and status columns provide the information needed for that decision.

Name the culture for every source, and keep the original text close by.

A parsed number is not proof of intent, it is only what one culture understood.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.