I got a situation where i need to handle special characters in incoming data, for example “USD100” or “100USD”. Here is the work around;
If we try like this, it will give back NULL value;
DECLARE @Value NVARCHAR(50) = '100USD';
SELECT TRY_CAST(@Value AS decimal);
The work around is this;
DECLARE @Value NVARCHAR(50) = '100USD';
SELECT TRY_CAST(REPLACE(REPLACE(REPLACE(@Value, 'USD', ''), '#', ''), '$', '') AS decimal);
Resources
https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15
https://www.sqlshack.com/replace-ascii-special-characters-sql-server/
Add to favorites
