Cast to decimal in Sql Server 2008

Question

I was using Try_Parse to convert an nvarchar value to decimal and it works fine in SQl Server 2012 and up but now I have to support the same query in 2008. The query is throwing an error. Is there a easy way to implement Try Parse in Sql Server 2008.

This seems to fail

select Cast('' as decimal)
select Cast('22#' as decimal)

Can I get a 0 value if the cast fails.

Thanks

What number formats do you *really* need to support? T-SQL's support for pattern matching is very limited, but really basic stuff like "contains only digits" is feasible (`NOT LIKE '%[^0-9]%'`) and often "good enough" when combined with a `CASE`. (There's also the old standby `ISNUMERIC`, of course, but that's a standby for a reason...) — Jeroen Mostert, Jan 28 '19 at 16:15
Well the numbers can easily be fit in decimal(18,2) but for some I am getting empty values which are failing to convert...so I was just looking at something which can just return me 0 if the conversion fails. — SP1, Jan 28 '19 at 16:18
Possible duplicate of [try\_parse in SQL Server 2008](https://stackoverflow.com/questions/30796861/try-parse-in-sql-server-2008) — Igor, Jan 28 '19 at 16:19
There's no such (exact) creature in 2008, which is why you have to be more specific. E.g. `DECLARE @t NVARCHAR(MAX) = '22#'; SELECT CONVERT(DECIMAL(18, 2), CASE WHEN @t NOT LIKE '%[^0-9]%' THEN @t ELSE '0' END);` will filter `22#`, but also `11.1`, while `ISNUMERIC` will not filter `$12`, even though that can't convert. `NOT LIKE '%[^0-9.]%'` will allow `11.1` but also `1.1.1.1`. Being very precise about what's allowed tends to be too complicated or slow when it has to be done in T-SQL, due to its poor support for string handling. — Jeroen Mostert, Jan 28 '19 at 16:21

score 0 · Answer 1 · answered Jan 28 '19 at 16:15

0

One way would be to change your set-based approach to a row-by-row approach and put your code in a TRY..CATCH block.

Another way would be to write a UDF that uses a TRY..CATCH block and call it from your set-based code.

answered Jan 28 '19 at 16:15

Tab Alleman

31,483
7
36
52

score 0 · Answer 2 · answered Jan 28 '19 at 16:25

Since you don't have the parsing features and you are using SQL Server 2008, this is an approach that would work. Not terribly efficient, but not many options. You could use something based on this try..catch pattern to write your own function to validate a decimal.

DECLARE @Values TABLE(
        ID INT IDENTITY(1,1)
    ,   [Value] VARCHAR(20)
)
INSERT INTO @Values SELECT '22.1'
INSERT INTO @Values SELECT '22.1 sdfsfds'

DECLARE @GoodValues TABLE(ID INT)
DECLARE @BadValues TABLE(ID INT)

DECLARE @ID INT
DECLARE @Value VARCHAR(20)
DECLARE @ValueAsDecimal DECIMAL

DECLARE tryparse_cursor CURSOR FOR   
SELECT ID, [Value]  
FROM @Values

OPEN tryparse_cursor  

FETCH NEXT FROM tryparse_cursor   
INTO @ID, @Value  

WHILE @@FETCH_STATUS = 0  
BEGIN
    BEGIN TRY
        SET @ValueAsDecimal=CAST(@Value AS DECIMAL);
        INSERT INTO @GoodValues SELECT @ID;
    END TRY
    BEGIN CATCH
        INSERT INTO @BadValues SELECT @ID;
    END CATCH

    FETCH NEXT FROM tryparse_cursor   
    INTO @ID, @Value   
END   
CLOSE tryparse_cursor;  
DEALLOCATE tryparse_cursor;  

select * from @GoodValues
select * from @BadValues

Cast to decimal in Sql Server 2008

2 Answers2