Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column Data type issue Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 11:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:57 PM
Points: 49, Visits: 392
Dear SSC,

I have a Decimal column.I'm not authorized to change the column data type.My data will be positive or negative value.What i need is, if the value will be negative means those data having prefix with "-" symbol but not in the Positive value.I want to insert the data with "+" symbol in the decimal column.
Is there any solutions?
Post #876585
Posted Thursday, March 4, 2010 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
Thamizh (3/3/2010)
Dear SSC,

I have a Decimal column.I'm not authorized to change the column data type.My data will be positive or negative value.What i need is, if the value will be negative means those data having prefix with "-" symbol but not in the Positive value.I want to insert the data with "+" symbol in the decimal column.
Is there any solutions?

No.
The decimal data type will display the '-' for negative numbers, but not the '+'.
I'd recommend to do it at the frontend side. If required to convert it with SQL server you'd need to change it to a character value and add the '+' notation with a CASE statement. I don't recommend it though...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #876672
Posted Thursday, March 4, 2010 6:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 11,194, Visits: 11,167
Seems an odd requirement, and the wrong place to do it as Lutz mentions.
Nevertheless...
DECLARE @Demo
TABLE (
data DECIMAL(5,2) NOT NULL,
odd_format AS
CASE SIGN(data)
WHEN -1 THEN CONVERT(VARCHAR(7), data)
-- Assumes zero is 'positive'
ELSE '+' + CONVERT(VARCHAR(7), data)
END
PERSISTED NOT NULL
);

INSERT @Demo (data)
SELECT TOP (20)
CHECKSUM(NEWID()) % 99999 / 100.0
FROM master.sys.all_columns;

SELECT *
FROM @Demo;

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #876774
Posted Thursday, March 4, 2010 6:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:57 PM
Points: 49, Visits: 392
No friends ,If i authorized for that means i will concatenate or alter the column .
But my seniors not allowed for that.That's y I'm asking
Post #876783
Posted Thursday, March 4, 2010 6:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 11,194, Visits: 11,167
If you're not authorized to change anything, it can't be changed

Do it application-side, or explain yourself clearly, and provide a valid reason for doing this.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #876789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse