# Convert negative varchar value to negative numeric

• coenie-353912

SSChasing Mays

Points: 615

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

• Dung Dinh

SSCrazy

Points: 2513

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

thanks, will try it

• ColdCoffee

SSC-Dedicated

Points: 39971

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: 4128

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

Another dig:

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

• ColdCoffee

SSC-Dedicated

Points: 39971

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: 10156

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
Be courteous. Drive responsibly.

• ColdCoffee

SSC-Dedicated

Points: 39971

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: 4128

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

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

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

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: 994661

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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• ColdCoffee

SSC-Dedicated

Points: 39971

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:

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

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