trimming trailing spaces and zeros from a string

Question

Post reply

trimming trailing spaces and zeros from a string

dvamartin

SSC Enthusiast

Points: 128
More actions
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 ?

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

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

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

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 2

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

guarddata SSCertifiable Points: 6583 More actions · Answer 3

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-

dvamartin SSC Enthusiast Points: 128 More actions · Answer 4

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

goinjm SSC Rookie Points: 41 More actions · Answer 5

goinjm

SSC Rookie

Points: 41

December 18, 2009 at 11:57 am

#1094235

Awesome script!!! Helped tremendously!!! 😀

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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)

goinjm SSC Rookie Points: 41 More actions · Answer 8

... 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,'')

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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)

goinjm SSC Rookie Points: 41 More actions · Answer 10

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

Thanks again for all the help!

Jim