CODEWITHSUNDEEP
sqlsql-servert-sql
Aman
Aman

Reputation: 295

How to place negative value into brackets

i want to place negative value in bracket like -2000 to (2000)

for that i made a which is converting -1361236.75886298 to (1.36124e+006)

the function that i made for this is :

ALTER function [dbo].[IsNegative](
@Number NVARCHAR (500)
)
Returns nvarchar (200)
as
begin 
Declare @Number2 FLOAT
set @Number2=Cast (@Number as float)
Declare @result nvarchar (200)
if ( @Number2  <=0.0)
begin
Set @result='('+Substring (@Number,2,len(@Number))+')'
end
else        
begin
Set @result = @Number 
end
return @result
end

i want accurate answere thanx in advance

Upvotes: 2

Views: 11172

Answers (3)

Shah
Shah

Reputation: 11

The solution can also be achieved with 'case'. For example,suppose I have a table a with column abc having values : 

select abc, case when abc>=0, abc else '(' + Convert(varchar,abs(abc),106) + ')' End From a

Upvotes: 1

Even Mien
Even Mien

Reputation: 45928

For SQL Server 2012 and higher, an alternative is to use the FORMAT function to enclose a negative number in parentheses.

A small limitation is that you will need to specify a maximum number of decimal places.

WITH T AS
(
SELECT -1361236.75886298 AS N UNION ALL
SELECT -2000 UNION ALL
SELECT 2000 
)

SELECT 
     FORMAT ( N, '#,###.##########;(#,###.##########)') 
FROM T

Results:

(1,361,236.75886298)
(2,000)
2,000

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453543

What is the proposed usage of this function?

This type of formatting is probably best left to the presentation layer (assuming that there is a presentation layer)

The following function might be suitable for formatting individual standalone values that are to be concatenated into a longer string. It would probably not be suitable for formatting multiple values as you would want these all to aligned at the decimal point. For this you could investigate the str function.

CREATE FUNCTION [dbo].[IsNegative](
@Number DECIMAL(38,16)
)
RETURNS VARCHAR (50)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN 
DECLARE @result VARCHAR (50)

/*Trim trailing zeroes and decimal point*/
SET @result = REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(@Number,'0', ' ')),' ', '0'),'.', ' ')),' ', '.')

IF @Number < 0
    SET @result = '(' + SUBSTRING(@result,2,LEN(@result)) + ')'

RETURN @result
END


GO

WITH T AS
(
SELECT -1361236.75886298 AS N UNION ALL
SELECT -2000 UNION ALL
SELECT 2000
)

SELECT 
     [dbo].[IsNegative](N)
FROM T

Returns

(1361236.75886298)
(2000)
2000

Is that what you need?

Upvotes: 3

Related Questions