Convert negative varchar value to negative numeric

Question

Post reply

Convert negative varchar value to negative numeric

coenie-353912

SSChasing Mays

Points: 615
More actions
September 14, 2010 at 12:18 am

#157199

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

Viewing 15 posts - 1 through 15 (of 16 total)

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

Dung Dinh SSCrazy Points: 2513 More actions · Answer 1

The simplest way is you use SUBSTRING fuction.

My example:

DECLARE @NegativeNumeric varchar(100)

SET @NegativeNumeric='50.10-'

SELECT (-1)*CAST(SUBSTRING(@NegativeNumeric,1,CHARINDEX('-',@NegativeNumeric)-1) AS numeric(18,2))

coenie-353912 SSChasing Mays Points: 615 More actions · Answer 2

coenie-353912

SSChasing Mays

Points: 615

September 14, 2010 at 12:46 am

#1220472

thanks, will try it

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

My two cents:

SET NOCOUNT ON

DECLARE @NegativeNumeric VARCHAR(100)

SELECT @NegativeNumeric='50.10-'

SELECT @NegativeNumeric = -1 * CAST ( LEFT (@NegativeNumeric , (LEN(@NegativeNumeric)-1)) AS DECIMAL (10,2))

SELECT @NegativeNumeric AS [ConvertedToNegativeNumber]

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 4

For what its worth ... Hopefully it adds some flexibility

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 5

Another dig:

SELECT @NegativeNumeric = -1 * CAST ( REPLACE(@NegativeNumeric ,'-','') AS DECIMAL (10,2))

SELECT @NegativeNumeric AS [ConvertedToNegativeNumber]

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 6

LTRIM & RTRIM from the above post forced me for this:

SET NOCOUNT ON

DECLARE @NegativeNumeric VARCHAR(100)

SELECT @NegativeNumeric='50.10-'

SELECT @NegativeNumeric = -1 * CAST ( REPLACE(LTRIM(RTRIM(@NegativeNumeric)) ,'-','') AS DECIMAL (10,2))

SELECT @NegativeNumeric AS [ConvertedToNegativeNumber]

SELECT @NegativeNumeric=' 454850.10000- '

SELECT @NegativeNumeric = -1 * CAST ( REPLACE(LTRIM(RTRIM(@NegativeNumeric)) ,'-','') AS DECIMAL (10,2))

SELECT @NegativeNumeric AS [ConvertedToNegativeNumber]

~Edit: Included trailing and leading spaces

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 7

Completely generic - no CAST/CONVERTS...

DECLARE @MyTable TABLE (MyNumericVal VARCHAR(100))

INSERT INTO @MyTable VALUES ('50.10-'),

('-50.10'),

('50.10'),

('abc')

SELECT CASE WHEN CHARINDEX('-',MyNumericVal) > 0 THEN SUBSTRING(MyNumericVal,CHARINDEX('-',MyNumericVal),LEN(MyNumericVal)) + SUBSTRING(MyNumericVal,0,CHARINDEX('-',MyNumericVal))

ELSE MyNumericVal

END

FROM @MyTable

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 8

Nakul Vachhrajani (9/14/2010)
Completely generic - no CAST/CONVERTS...
DECLARE @MyTable TABLE (MyNumericVal VARCHAR(100))
INSERT INTO @MyTable VALUES ('50.10-'),
('-50.10'),
('50.10'),
('abc')
SELECT CASE WHEN CHARINDEX('-',MyNumericVal) > 0 THEN SUBSTRING(MyNumericVal,CHARINDEX('-',MyNumericVal),LEN(MyNumericVal)) + SUBSTRING(MyNumericVal,0,CHARINDEX('-',MyNumericVal))
ELSE MyNumericVal
END
FROM @MyTable

With all due respect to ur wonderful code, the OP's request was to convert it to a numeric value. His quote:

the minus sign is at the end of the value, to a negative numeric value (-50.10)

So IMHO, we WILL need CASTs and CONVERTs...

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 9

Nakul Vachhrajani (9/14/2010)
Completely generic - no CAST/CONVERTS...
DECLARE @MyTable TABLE (MyNumericVal VARCHAR(100))
INSERT INTO @MyTable VALUES ('50.10-'),
('-50.10'),
('50.10'),
('abc'),
(' 50.10- ')
SELECT CASE WHEN CHARINDEX('-',MyNumericVal) > 0 THEN SUBSTRING(MyNumericVal,CHARINDEX('-',MyNumericVal),LEN(MyNumericVal)) + SUBSTRING(MyNumericVal,0,CHARINDEX('-',MyNumericVal))
ELSE MyNumericVal
END
FROM @MyTable

Forgot the LTRIM and this returns a Character!:-P

lol - Ten Centuries beat me to it!

I hope we gave the OP some ideas!

coenie-353912 SSChasing Mays Points: 615 More actions · Answer 10

Thanks SSC-Enthusiastic

I forgot to mention that some values do not have a negative at the end, but your code worked.

Thanks again for all the replies.

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 11

Would this do ?

DECLARE @MyTable TABLE (MyNumericVal VARCHAR(100))

INSERT INTO @MyTable VALUES ('50.10-'),

('-50.10'),

('50.10'),

(' 50.10- ')

SELECT CASE

WHEN CHARINDEX('-',MyNumericVal) > 0

THEN -1 * CAST ( REPLACE(LTRIM(RTRIM(MyNumericVal)) ,'-','') AS DECIMAL (10,2))

ELSE

MyNumericVal

END AS Converted

FROM @MyTable

coenie-353912 SSChasing Mays Points: 615 More actions · Answer 12

Thanks SSC-Enthusiastic

this worked for me, I forgot to mentioned that some values be negative, but 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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 13

coenie-353912 (9/14/2010)
Thanks SSC-Enthusiastic
this worked for me, I forgot to mentioned that some values be negative, but 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

You might want to take a gander at the simplicity of ColdCoffee's code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 14

Jeff Moden (9/14/2010)
You might want to take a gander at the simplicity of ColdCoffee's code. 😉

I was telling the OP to have a look at that from the start Jeff, he wasn't interested :-P.. pro'lly he dislikes any form of Coffee :hehe: