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