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

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

    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.

    Follow me on
    Twitter: @sqltwins

  • 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: 4125

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

    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. 😉

    Helpful Links:
    How to post code problems

  • 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