Post reply

removed zeroes from numeric column

  • February 18, 2019 at 11:25 am

    #409519

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    ThanksSaurabh.D

  • February 18, 2019 at 11:41 am

    #2022300

    What do you mean remove zeros?

  • February 18, 2019 at 11:46 am

    #2022301

    Saurabh.D - Monday, February 18, 2019 11:25 AM

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    You could still use replace - REPLACE(Salary, 0, '') - without explicitly converting the values but the SQL statement will still do an implicit conversion

    Sue

  • February 18, 2019 at 2:12 pm

    #2022315

    Sue_H - Monday, February 18, 2019 11:46 AM

    Saurabh.D - Monday, February 18, 2019 11:25 AM

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    You could still use replace - REPLACE(Salary, 0, '') - without explicitly converting the values but the SQL statement will still do an implicit conversion

    Sue

    AND, you would end up with a string as a result instead of a numeric data type.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • February 18, 2019 at 2:19 pm

    #2022317

    sgmunson - Monday, February 18, 2019 2:12 PM

    Sue_H - Monday, February 18, 2019 11:46 AM

    Saurabh.D - Monday, February 18, 2019 11:25 AM

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    You could still use replace - REPLACE(Salary, 0, '') - without explicitly converting the values but the SQL statement will still do an implicit conversion

    Sue

    AND, you would end up with a string as a result instead of a numeric data type.

    Yup but it could also depend upon how it's being used or where you are doing what, how it's being used
    What I mean by that is you can do an update when the column is int, data type for the column won't change.

    Sue

  • February 18, 2019 at 2:25 pm

    #2022318

    Sue_H - Monday, February 18, 2019 2:19 PM

    Yup but it could also depend upon how it's being used or where you are doing what, how it's being used
    What I mean by that is you can do an update when the column is int, data type for the column won't change.

    Sue

    I'm more curious why they want to remove 0's from the middle of a number, especially a salary....

  • February 18, 2019 at 2:27 pm

    #2022319

    ZZartin - Monday, February 18, 2019 2:25 PM

    I'm more curious why they want to remove 0's from the middle of a number, especially a salary....

    They'd save a lot of money on labor costs  🙂

  • February 18, 2019 at 2:36 pm

    #2022321

    Sue_H - Monday, February 18, 2019 2:27 PM

    They'd save a lot of money on labor costs  🙂

    Either that or it's IRAQ and they want to "remove the zeroes from the dinar", which is how many translations of articles on topics impacting the value of the IQD over the last 10 years.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • February 18, 2019 at 4:43 pm

    #2022331

    Saurabh.D - Monday, February 18, 2019 11:25 AM

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    It can easily be done using iterative INTEGER division and multiplication.  Why would you want to or need to do this? If you can tell me that, I'll show you how to do it in a set based manner with no WHILE loop and no Recursive CTE (which qualifies as a hidden loop in this case).

    --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)

  • February 19, 2019 at 1:54 am

    #2022350

    Saurabh.D - Monday, February 18, 2019 11:25 AM

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    weird request. or not correctly formulated.
    removing zeros from the middle of an int value just means scrambles all values - might as well use dummy values instead

    Following code will remove all zeros - final result is still an int - obviously it requires a intermediary string step

    select t.id
      , t.name
      , convert(int, replace(convert(varchar(30), t.value), '0', '')) as value_no_zeros
    from (values (1, 'Kristeen', 1420)
        , (2, 'Ashley', 2006)
        , (3, 'Julia', 2210)
        , (4, 'Maria', 3000)
      ) t(id, name, value)
    1    Kristeen  142
    2    Ashley     26
    3    Julia     221
    4    Maria       3

  • February 19, 2019 at 6:12 am

    #2022361

    frederico_fonseca - Tuesday, February 19, 2019 1:54 AM

    Saurabh.D - Monday, February 18, 2019 11:25 AM

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    weird request. or not correctly formulated.
    removing zeros from the middle of an int value just means scrambles all values - might as well use dummy values instead

    Following code will remove all zeros - final result is still an int - obviously it requires a intermediary string step

    select t.id
      , t.name
      , convert(int, replace(convert(varchar(30), t.value), '0', '')) as value_no_zeros
    from (values (1, 'Kristeen', 1420)
        , (2, 'Ashley', 2006)
        , (3, 'Julia', 2210)
        , (4, 'Maria', 3000)
      ) t(id, name, value)
    1    Kristeen  142
    2    Ashley     26
    3    Julia     221
    4    Maria       3

    I've got a way to do it without ever going through a string step.  I'm holding in back in return for the OP explaining why he'd want to do such an unusual thing.

    --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)

  • February 19, 2019 at 7:47 am

    #2022369

    Super easy when using string functions (of course) but, for fun, I took a stab at this. It's a little harder when the number is not evenly divisible by 10 but not too hard (I just ran out of time). This will get the numbers that end in one or more zeros. 

    DECLARE @table TABLE (ID INT IDENTITY, [Name] VARCHAR(100), Salary INT);
    INSERT @table (Name, Salary)
    VALUES ('Kristen', 1420),('Ashley', 2006), ('Julie',2210),('Maria',3000);

    SELECT t.ID, t.[Name], t.Salary, f.NewNumber
    FROM @table AS t
    CROSS APPLY
    (
    SELECT t.Salary/MAX(f.D)
    FROM @table AS t2
    CROSS APPLY (VALUES(10),(100),(1000)) AS f(D)
    WHERE t.ID = t2.ID AND t.Salary%f.D = 0
    ) AS f(NewNumber)
    WHERE f.NewNumber IS NOT NULL;

    Returns:
    ID Name  Salary  NewNumber
    --------------------------------
    1  Kristen 1420  142
    3  Julie  2210  221
    4  Maria  3000  3

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • February 19, 2019 at 10:24 am

    #2022399

    Here's a way to do it using only mathematical operations, I think it will be less efficient than using a replace though.

    DECLARE @table TABLE (ID INT IDENTITY, [Name] VARCHAR(100), Salary INT);
    INSERT @table (Name, Salary)
    VALUES ('Kristen', 1420),('Ashley', 2006), ('Julie',2210),('Maria',3000);
    DECLARE @MaxLengthOfInt int = 4; /* maximum length of the column to remove zeros from */

    WITH Tally AS (SELECT TOP(@MaxLengthOfInt) * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) N(N)),
    NewDigits AS (SELECT t.ID,
                          x.DigitValue*POWER(10,COUNT(n.N) OVER (PARTITION BY t.ID ORDER BY n.N)-1) newval
                     FROM @table t
                    CROSS JOIN Tally n
                    CROSS APPLY (VALUES (t.Salary%POWER(10,n.N)/POWER(10,n.N-1))) x(DigitValue)
                    WHERE x.DigitValue <> 0
    )
    SELECT t.ID,
           t.Name,
           t.Salary,
           SUM(newval) SalaryWithZerosRemoved
      FROM @table t
     INNER JOIN NewDigits x on x.ID = t.ID
     GROUP BY t.ID, t.Name, t.Salary
    Jeff, I wondered if your solution was similar?

  • February 19, 2019 at 10:48 am

    #2022405

    Jeff Moden - Tuesday, February 19, 2019 6:12 AM

    frederico_fonseca - Tuesday, February 19, 2019 1:54 AM

    Saurabh.D - Monday, February 18, 2019 11:25 AM

    I am curious to know, if there is any way to remove zeroes from numeric column without converting them into string.

    weird request. or not correctly formulated.
    removing zeros from the middle of an int value just means scrambles all values - might as well use dummy values instead

    Following code will remove all zeros - final result is still an int - obviously it requires a intermediary string step

    select t.id
      , t.name
      , convert(int, replace(convert(varchar(30), t.value), '0', '')) as value_no_zeros
    from (values (1, 'Kristeen', 1420)
        , (2, 'Ashley', 2006)
        , (3, 'Julia', 2210)
        , (4, 'Maria', 3000)
      ) t(id, name, value)
    1    Kristeen  142
    2    Ashley     26
    3    Julia     221
    4    Maria       3

    I've got a way to do it without ever going through a string step.  I'm holding in back in return for the OP explaining why he'd want to do such an unusual thing.

    C'mon Jeff.  Now you're just teasing everybody 😉

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 19, 2019 at 6:58 pm

    #2022445

    Mike01 - Tuesday, February 19, 2019 10:48 AM

    C'mon Jeff.  Now you're just teasing everybody 😉

    Crud... that's certainly not my intention.  The reason I'm not posting is because the original post on this thread smells too much like the "Presidents' Birthdays" thread.

    --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)

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

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