Column Data type issue

Question

Column Data type issue

Thamizh

Old Hand

Points: 329
More actions
March 3, 2010 at 11:44 pm

#94437

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?

Viewing 5 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 2

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Thamizh Old Hand Points: 329 More actions · Answer 3

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

Paul White SSC Guru Points: 150467 More actions · Answer 4

If you're not authorized to change anything, it can't be changed :laugh:

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

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi