Decimal Datatype with negative value

Question

Post reply

Decimal Datatype with negative value

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

kelly.fergus@gmail.com Old Hand Points: 372 More actions · Answer 1

ok noted about TEXT - thank you

So would I be good to temporarily store my figure in a VARCHAR(50) ?

Thanks

G

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

kelly.fergus@gmail.com wrote:

ok noted about TEXT - thank you
So would I be good to temporarily store my figure in a VARCHAR(50) ?
Thanks
G

Probably larger than you need, but should be fine.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kelly.fergus@gmail.com Old Hand Points: 372 More actions · Answer 3

great i will go with that.

so my final issue with my trailing minus sign is updating all columns that have it in it. I

UPDATE Data_Temp
SET TotalBal = '-' + SUBSTRING(TotalBal, 1, LEN(TotalBal) - 1)
WHERE TotalBal LIKE '%-';

can do this on one column but when I try on 2 or 3 columns i am getting errors. I have 10 columns where this trailing minus sign appears so would like to apply above snippet to all of them without writing 10 update sql scripts

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Here's a possible way. It assumes that if a hyphen appears anywhere in the underlying text string, it must mean that the string is a negative number.

DROP TABLE IF EXISTS #temp;

CREATE TABLE #temp
(
    col1 VARCHAR(50)
   ,col2 VARCHAR(50)
);

INSERT #temp
(
    col1
   ,col2
)
VALUES
('1', '2')
,('1', '2-')
,('-1', '2')
,('1-', '2-')
,('-1', '2  -');

SELECT *
FROM #temp t;

UPDATE #temp
SET col1 = IIF(LEN (col1) <> LEN (REPLACE (col1, '-', '')), CONCAT ('-', REPLACE (col1, '-', '')), col1)
   ,col2 = IIF(LEN (col2) <> LEN (REPLACE (col2, '-', '')), CONCAT ('-', REPLACE (col2, '-', '')), col2);

SELECT *
FROM #temp t;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kelly.fergus@gmail.com Old Hand Points: 372 More actions · Answer 5

oh beautifully done.

Thank you!

I can now start to move my project on!