Post reply

trimming trailing spaces and zeros from a string

  • May 19, 2003 at 8:06 am

    #80159

    how would I trim trailing spaces and zeros from a string like this ?

    "345.2300" to get "345.23

    and "25.0000 " to get "25" i.e. no decimal place ?

  • May 19, 2003 at 8:43 am

    #457641

    You might try something like this:

    declare @d decimal(10,5)

    set @d = 25.000

    select case when @d = floor(@d) then CAST(floor(@d) AS CHAR) else rtrim(cast(floor(@d) as char)) + '.' + REVERSE(RTRIM(CAST(CAST(floor(reverse(cast(@d as char(10))))AS INT)AS CHAR))) end

    set @d = 345.23

    select case when @d = floor(@d) then CAST(floor(@d) AS CHAR) else rtrim(cast(floor(@d) as char)) + '.' + REVERSE(RTRIM(CAST(CAST(floor(reverse(cast(@d as char(10))))AS INT)AS CHAR))) end

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • May 19, 2003 at 8:46 am

    #457643

    Alternatively, try a user defined function:

    create function dbo.fn_RemoveTrailingZeros ( @string varchar(25) )

    returns varchar(25) as

    begin

    declare @loop char(1)

    set @loop = 'Y'

    while @loop = 'Y' begin

    if right(@string,1) not in ('0','.') begin

    set @loop = 'N'

    end

    else set @string = left(@string,len(@string)-1)

    end

    return (@string)

    end

    go

    select dbo.fn_RemoveTrailingZeros('123456.7890')

    select dbo.fn_RemoveTrailingZeros('123.000')

    Jeremy

  • May 19, 2003 at 9:52 am

    #457654

    Wish I could remember the one that produced this a while ago. I can't take credit here.

    If you have

    DECLARE @numStr

    SET @numStr = '345.2300'

    then

    SELECT @numStr = REPLACE( RTRIM( REPLACE( REPLACE( RTRIM( REPLACE( @numStr, '0', ' ' ) ), ' ', '0' ), '.', ' ' ) ), ' ', '.' )

    Guarddata-

  • May 19, 2003 at 10:34 am

    #457664

    Guarddata, you are trully a genious !!!

    I got it working with a convolluted set of case, LEN, RTRIM etc etc statement and view but this is 1000% cleaner and faster

  • December 18, 2009 at 11:57 am

    #1094235

    Awesome script!!! Helped tremendously!!! 😀

  • December 19, 2009 at 9:03 am

    #1094457

    guarddata (5/19/2003)

    Wish I could remember the one that produced this a while ago. I can't take credit here.

    If you have

    DECLARE @numStr

    SET @numStr = '345.2300'

    then

    SELECT @numStr = REPLACE( RTRIM( REPLACE( REPLACE( RTRIM( REPLACE( @numStr, '0', ' ' ) ), ' ', '0' ), '.', ' ' ) ), ' ', '.' )

    Guarddata-

    Be careful.... doesn't work the way you think it does.... try it with this...

    DECLARE @numStr VARCHAR(100)

    SET @numStr = '340'

    SELECT REPLACE( RTRIM( REPLACE( REPLACE( RTRIM( REPLACE( @numStr, '0', ' ' ) ), ' ', '0' ), '.', ' ' ) ), ' ', '.' )

    --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)
    Intro to Tally Tables and Functions

  • December 19, 2009 at 10:03 am

    #1094461

    First of all, I have to remind everyone that SQL Server isn't a word processor and storing formatted text numbers is very contrary to what SQL Server should be used for. The proper "fix" would be to change the column datatype to some reasonable decimal data type.

    With that in mind, let's find out the best way to do this by asking... why do you want to do this? Why is it necessary to trim trailing zero's so that the results come out in a "ragged right" fashion like this?

    --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)
    Intro to Tally Tables and Functions

  • December 21, 2009 at 7:20 am

    #1094745

    ... why do you want to do this? Why is it necessary to trim trailing zero's so that the results come out in a "ragged right" fashion like this?

    Thanks for your response Jeff, I didn't think of testing against a number that doesn't contain a decimal, i'll need to keep that in mind. The variable I begin with is a decimal (15,5) so I didn't run into that scenario when a value was 100.00000 for instance.

    The reason?...I'm running a procedure that will pass a string to an excel document...no chuckles on excel here please :-)..., i'm concatenating 4 fields; one of which is this number that cannot have trailing zero's for readability sake, then inserting a comma, followed by the next row's 4 field values if present. (I beleive this is using the STUFF example you provided in another forum) The reason for all the formatting is only one row can be returned to a Dot.Net app that then pushes the information into an Excel application's named_range.

    stuff((select ', ' + d.field1 + ' ' + d.field2 + ' ' + cast(replace(rtrim(replace(replace(rtrim(replace(e.field3,'0',' ')),' ','0'),'.',' ')),' ','.') AS varchar(15)) + ' ' + e.field4

    from b

    join e on b.sys_id = e.b_sys_id

    and valueA is null

    join d on e.d_code = d.code

    where b.sys_id = a.sys_id

    for xml path('')),1,1,'')

  • December 21, 2009 at 7:53 am

    #1094773

    Heh... got it and thanks for taking the time to explain.

    Depending on how you're pushing that to Excel, it's likely that the trailing 0's just won't matter... Excel will have the final say so as to what the number looks like based on cell formatting.

    Do you still need the ability to do this while considering numbers with both decimal points and without?

    --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)
    Intro to Tally Tables and Functions

  • December 21, 2009 at 8:16 am

    #1094789

    Not necessary for both decimal and non-deecimal, will only be pushing decimal numebrs through the conversion.

    Thanks again for all the help!

    Jim

Viewing 11 posts - 1 through 10 (of 10 total)

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