CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Akshay Mishra
Akshay Mishra

Reputation: 35

Converting Varchar to negative decimal in brackets

I want to change a String to a negative decimal value. The string is in the below format: ($421.24)

I want to change this Varchar to decimal to show either -421.24 or (421.24). I am using Replace function to achieve that but that's not a cool way:

Select convert(decimal(18,2), Replace(Replace(Replace('($421.24)','$', '' ),'(','-'),')','')) -- output -421.24

I want to make it a general sql staement which holds true for both positive and negative numbers. Please suggest.

Upvotes: 1

Views: 2990

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82504

Here are some shorter options:

  1. Use a single replace to replace both the ( and the $ sign in one go:
    Select CAST(Replace(Replace('($421.24)','($', '-'), ')', '') As decimal(18,2))

  2. Use substring instead of replace:
    Select CAST('-' + SUBSTRING('($421.24)',3, LEN('($421.24)') - 3) As decimal(18,2))

  3. For SQL Server version 2017 or later, you can use translate, to replace multiple characters in a single command:
    Select CAST(TRANSLATE('($421.24)', '($)', ' - ') As decimal(18,2))

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Take the string and by using a WHILE loop, take each character and check whether it is a . or a number. If yes then take that character else neglect it and finally cast it to a decimal value.

Query

declare @str as varchar(100) = '($421.24)';
declare @len as int;
select @len = len(@str);
declare @i as int;
set @i = 1;
declare @res as varchar(100) = '';
while(@i <= @len)
begin
    declare @c as char(1);
    select @c = substring(@str, @i, 1);
    select @res+= case when ASCII(@c) = 46 or (ASCII(@c) between 48 and 57)
      then @c else '' end;

    set @i += 1;
end

select cast(@res as decimal(18, 2)) as [decimal_val];

Find a demo here

Note: You may need to check for multiple dots.

Update

If the string is in a format which cannot convert to a decimal value, then add the below after the WHILE loop.

Query

declare @isnum as bit;
select @isnum = isnumeric(@res);
if(@isnum = 1)
    select cast(@res as decimal(18, 2));
else 
    select 'String cannot convert to a decimal value.';

An other demo

Upvotes: 0

Related Questions