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: 246711 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 246711 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;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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!