convert negatvie value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

convert negatvie value

Author

Topic

conradao.co.za
Starting Member

6 Posts

Posted - 2010-09-14 : 02:15:46

How do I convert a negative varchar value (50.10-), the minus sign is at the end of the value, to a negative numeric value (-50.10)

Thanks

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 03:14:15

declare @val as varchar(20)='(50.10-)'
select case when right(substring(reverse(@val),2,1),2)='-' then replace(replace(@val,'-)',')'),'(','(-')end

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 05:20:52

quote:
Originally posted by conradao.co.za

How do I convert a negative varchar value (50.10-), the minus sign is at the end of the value, to a negative numeric value (-50.10)

Thanks

You should always use proper numeric datatype to store these values and leave the formation at front end application

Madhivanan

Failing to plan is Planning to fail

conradao.co.za
Starting Member

6 Posts

Posted - 2010-09-14 : 05:54:36

thanks, got the answer this works

DECLARE @l_CharVal VARCHAR(20)

SET @l_CharVal = ' 50.12- '

SELECT
CASE
WHEN CHARINDEX('-', LTRIM(RTRIM(@l_CharVal))) = LEN(LTRIM(@l_CharVal))
THEN CONVERT(DECIMAL(5, 2), LEFT(LTRIM(RTRIM(@l_CharVal)), LEN(LTRIM(@l_CharVal)) - 1)) * -1 -- We know this has a trailing "-"
ELSE CONVERT(DECIMAL(5, 2), LTRIM(RTRIM(@l_CharVal)))
END
from aatest

conradao.co.za
Starting Member

6 Posts

Posted - 2010-09-14 : 06:38:09

Thanks Madhivanan for your reply

and I agree with you, but when you work with a database that is 20 years old and you need to clean up the data, it is a bit difficult to go back to the original developers and ask them to change the app code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources