Interpreting a comma as grouping or decimal punctuation changes the meaning of a price. Currency strings need a known formatting culture before their punctuation becomes a numeric value.
Keep Currency and Formatting Separate
A monetary amount needs both a numeric value and a currency identifier. Culture describes how text is formatted, not which currency the amount represents. Do not assume that an English-formatted value always represents USD.
This demonstration accepts two explicitly defined source formats. USD values use a leading dollar symbol with en-US formatting. EUR values use a trailing EUR token with de-DE formatting.
I preserve the raw text when reviewing an import conversion. I also retain the source currency and culture independently. Once punctuation is removed, some original interpretation evidence is gone.
Use a fresh isolated session for the temporary staging example. The fixture includes two valid formatted amounts and deliberate bad rows. These are chosen test inputs, not observed import results.
CREATE TABLE #PriceImport
(
ImportId int NOT NULL PRIMARY KEY,
RawPrice nvarchar(100) NOT NULL,
CurrencyCode char(3) NOT NULL,
SourceCulture varchar(10) NOT NULL
CHECK (SourceCulture IN ('en-US', 'de-DE'))
);
INSERT #PriceImport VALUES
(1,N'$1,234.50','USD','en-US'),
(2,N'1.234,50 EUR','EUR','de-DE'),
(3,N'$42.00','USD','en-US'),
(4,N'$bad','USD','en-US'),
(5,N' ','USD','en-US'),
(6,N'1.234,50 USD','EUR','de-DE');Remove Only Recognized Tokens From Currency Strings
A general replacement that deletes every nonnumeric character is unsafe. It can turn corrupted text into an apparently valid number. Remove the exact token permitted by the known source contract instead.
The following cleanup first normalizes a nonbreaking space to an ordinary space. It then trims surrounding whitespace and verifies the required currency token. A mismatched token leaves NumberText NULL for later rejection.
The USD branch removes only its leading symbol. The EUR branch removes only its defined trailing token. It does not guess the currency by searching for a familiar letter anywhere in the string.
WITH Trimmed AS
(
SELECT ImportId, RawPrice, CurrencyCode, SourceCulture,
LTRIM(RTRIM(REPLACE(RawPrice, NCHAR(160), N' '))) AS TrimmedPrice
FROM #PriceImport
)
SELECT ImportId, RawPrice, CurrencyCode, SourceCulture,
CASE
WHEN CurrencyCode = 'USD' AND SourceCulture = 'en-US'
AND LEFT(TrimmedPrice, 1) = N'$'
THEN LTRIM(RTRIM(SUBSTRING(TrimmedPrice, 2, 100)))
WHEN CurrencyCode = 'EUR' AND SourceCulture = 'de-DE'
AND RIGHT(TrimmedPrice, 4) = N' EUR'
THEN LTRIM(RTRIM(LEFT(TrimmedPrice, LEN(TrimmedPrice) - 4)))
ELSE NULL
END AS NumberText
INTO #PriceText
FROM Trimmed;Do not remove internal spaces indiscriminately unless the source format authorizes them as grouping characters. A value such as one space two can hide an input defect. Outer trimming and arbitrary interior deletion have different meanings.
Other symbols, parentheses, and negative-value conventions need explicit policies. Extend the contract and tests before accepting another format. Adding more REPLACE calls is not a substitute for specifying what valid input looks like.
Parse Currency Strings With an Explicit Culture
TRY_PARSE can interpret numeric text under a specified supported culture. For decimal parsing, the symbol has already been removed by the preceding step. Relying on the connection language would make the interpretation less explicit.
The staged culture values are restricted to the two approved names. An invalid culture argument can raise an error rather than returning a bad-row NULL. Validate culture metadata before it reaches the parser.
SELECT ImportId, RawPrice, CurrencyCode, SourceCulture, NumberText,
TRY_PARSE(NumberText AS decimal(19,2) USING SourceCulture) AS ParsedAmount,
CASE WHEN SourceCulture = 'en-US'
THEN REPLACE(NumberText, N',', N'')
WHEN SourceCulture = 'de-DE'
THEN REPLACE(REPLACE(NumberText, N'.', N''), N',', N'.')
END AS CanonicalText
INTO #PriceParsed
FROM #PriceText;
SELECT *, TRY_CONVERT(decimal(19,2), NULLIF(CanonicalText, N'')) AS ConvertedAmount
INTO #PriceChecked
FROM #PriceParsed;
SELECT * FROM #PriceChecked ORDER BY ImportId;TRY_PARSE uses the .NET Framework runtime and carries parsing overhead. It also cannot be remoted to another server. Test its supported environment and throughput before adopting it for a large import.
For high-volume known formats, explicit normalization plus TRY_CONVERT can be a useful alternative. Keep the normalization culture-specific as shown. Removing all commas globally would change the German decimal separator's meaning.
Separate Conversion Failure From Format Validation
TRY_CONVERT returns NULL for these failed text-to-decimal conversions. It does not make every permitted conversion a valid business price. Conversion, currency consistency, and allowed sign are separate checks.
The example requires both conversion paths to produce the same nonnegative amount. It rejects missing numeric text and parsing failures. Agreement provides a useful conversion check but does not validate every possible grouping pattern.
Culture parsers can accept punctuation arrangements that a strict source grammar should reject. If grouping positions matter, validate that grammar before parsing. A successful parser call alone does not prove correctly grouped thousands.
Decimal scale also has a policy consequence. Conversion to decimal(19,2) can round values with extra fractional digits. Reject excessive fractional precision separately when the source contract requires exact two-place amounts rather than rounding.
SELECT ImportId, RawPrice, CurrencyCode, SourceCulture,
CASE
WHEN NumberText IS NULL OR NumberText = N'' THEN 'Missing or mismatched token'
WHEN ParsedAmount IS NULL OR ConvertedAmount IS NULL THEN 'Invalid decimal text'
WHEN ParsedAmount <> ConvertedAmount THEN 'Interpretation mismatch'
WHEN ParsedAmount < 0 THEN 'Negative price not permitted'
END AS RejectionReason
INTO #PriceRejected
FROM #PriceChecked
WHERE NumberText IS NULL OR NumberText = N''
OR ParsedAmount IS NULL OR ConvertedAmount IS NULL
OR ParsedAmount <> ConvertedAmount OR ParsedAmount < 0;
SELECT * FROM #PriceRejected ORDER BY ImportId;Store Accepted Values in decimal Columns
The destination holds typed decimal amounts rather than formatted text. Retain the currency code beside each amount. Display formatting belongs to the consuming application and should not redefine stored arithmetic.
The sample loads accepted rows while retaining rejected rows separately in its temporary evidence. That is an explicit partial-acceptance demonstration. A real all-or-nothing batch should stop before inserting any accepted row when a rejection exists.
CREATE TABLE #CleanPrice
(
ImportId int NOT NULL PRIMARY KEY,
CurrencyCode char(3) NOT NULL,
Amount decimal(19,2) NOT NULL CHECK (Amount >= 0)
);
INSERT #CleanPrice(ImportId, CurrencyCode, Amount)
SELECT C.ImportId, C.CurrencyCode, C.ConvertedAmount
FROM #PriceChecked AS C
WHERE NOT EXISTS
(
SELECT 1 FROM #PriceRejected AS R
WHERE R.ImportId = C.ImportId
);
SELECT ImportId, CurrencyCode, Amount
FROM #CleanPrice ORDER BY ImportId;Use a stable import identifier to connect accepted and rejected evidence to the source. Reprocessing needs an explicit duplicate policy rather than another blind insert. Production staging and rejection records need an approved durable retention process.
Never replace failed conversions with zero merely to satisfy a NOT NULL column. Zero is a valid numeric statement about price. An invalid string is an unresolved data issue with a different meaning.
Test Dangerous Currency Strings Deliberately
Would the same punctuation represent another value under a different culture? Put that ambiguity into the test set. Include boundary amounts, empty text, unexpected tokens, and excessive fractional precision.
On my run, rows 1 to 3 loaded as 1234.50, 1234.50 and 42.00. Row 4 was rejected as invalid decimal text, and rows 5 and 6 as missing or mismatched tokens. Compare numeric amounts independently from their display formatting.
A grouping validator should reject malformed arrangements according to the approved source grammar. Do not assume two agreeing conversion functions independently verify that grammar. Their acceptance rules can overlap in permissive ways.
Large amounts can also exceed the destination precision even when their text is well formed. Include overflow cases in the rejection process. A wider staging type does not automatically authorize a wider business amount.
Keep Arithmetic Independent of Presentation
Currency strings are import evidence, not the preferred format for stored prices. Typed decimal values provide controlled precision for arithmetic. Mixing currencies in one SUM still requires an approved conversion or grouping rule.
I convert currency strings only after identifying their source conventions. I keep failures visible until the producer resolves them. Punctuation should not receive a promotion to financial decision maker.
Document the currency, formatting culture, precision, sign, and rounding rules together. Revalidate the parser whenever a new feed format arrives. The durable value should reflect an agreed interpretation rather than a lucky replacement sequence.
The parser is one stage in data acceptance. Token checks, grammar rules, numeric conversion, and business limits serve different purposes. Keeping those stages visible makes both successful loads and rejected prices easier to explain.
Related reading on this blog: Datatype Decimal Explained: Datatype Numeric and "Clean Data" Is Not a Requirement: Writing Rules People Can Act On.
A cleaned price is not text with its symbols removed, it is a validated decimal amount with a known currency.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
