473,836 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trailing 0's

I have a column defined as DECIMAL(10,6). I want to display it as a
string but I do not want the trailing zeros. I cannot seem to get CAST
or CONVERT or STR to exclude the zeros.

Examples:
45.340000 --> 45.34
27.700000 --> 27.7
55.000000 --> 55

Is there a function that will do this or do I need to write my own?
Jul 20 '05 #1
5 15482

"Jason" <Ja*******@hotm ail.com> wrote in message
news:f0******** *************** ***@posting.goo gle.com...
I have a column defined as DECIMAL(10,6). I want to display it as a
string but I do not want the trailing zeros. I cannot seem to get CAST
or CONVERT or STR to exclude the zeros.

Examples:
45.340000 --> 45.34
27.700000 --> 27.7
55.000000 --> 55

Is there a function that will do this or do I need to write my own?


It's usually best to do this formatting in the client, rather than the
database layer - client tools will have much better formatting options, and
they allow for changing the formatting dynamically without having to
resubmit the query. But here's one way to do it:

select case
when right(replace(c onvert(varchar, MyColumn), '0', ''), 1) = '.'
then replace(replace (convert(varcha r, MyColumn), '0', ''), '.', '')
else replace(convert (varchar, MyColumn), '0', '')
end
from dbo.MyTable

Simon
Jul 20 '05 #2
Hi Jason,

I'm not sure if you can find a function in TSQL that does what you
want in this case. But you can use a bunch of string functions to do
the trick, like this:
select rtrim(
replace(
replace(
rtrim(
replace(
cast(DecField as char(11)),
'0',
' '
)
),
' ',
'0'
) + ' ',
'. ',
''
)
)
from YourTable

Let me explain what it does.
Assume you have these decimal values: 43.035000 and 55.000000

1. Convert decimal value into string
x = cast(DecField as char(11))
'43.035000 '
'55.000000 '

2. Replace 0's with space
x = replace(x, '0', ' ')
'43. 35 '
'55. '

3. Trim the trailing spaces
x = RTrim(x)
'43. 35'
'55.'

4. Replace spaces with 0
x = replace(x, ' ', '0')
'43.035'
'55.'

As you see by now all noninteger values are ready, but still we need
to get rid of decimal point appearing at the end of integer values.

5. Add a single space to the end
x = x + ' '
'43.035 '
'55. '

6. Replace '. ' with an empty string
x = replace(x, '. ', '')
'43.035 '
'55'

7. Finally get rid of remaining spaces
x = RTrim(x)
'43.035'
'55'
Well, this works fine but it's not the most efficient to do it.
Depending on size of your table and the number of decimal fields you
want to show, it might take a while. I personally prefer to perform
such cosmetic transformations in front-end application at the time you
display the values. Of course it depends on your programming
environment and its features.

Good luck,
Shervin

Ja*******@hotma il.com (Jason) wrote in message news:<f0******* *************** ****@posting.go ogle.com>...
I have a column defined as DECIMAL(10,6). I want to display it as a
string but I do not want the trailing zeros. I cannot seem to get CAST
or CONVERT or STR to exclude the zeros.

Examples:
45.340000 --> 45.34
27.700000 --> 27.7
55.000000 --> 55

Is there a function that will do this or do I need to write my own?

Jul 20 '05 #3
Hi Simon,

This script removes all the 0's, not just trailing ones.

Shervin

"Simon Hayes" <sq*@hayes.ch > wrote in message news:<3f******* ***@news.bluewi n.ch>...
"Jason" <Ja*******@hotm ail.com> wrote in message
news:f0******** *************** ***@posting.goo gle.com...
I have a column defined as DECIMAL(10,6). I want to display it as a
string but I do not want the trailing zeros. I cannot seem to get CAST
or CONVERT or STR to exclude the zeros.

Examples:
45.340000 --> 45.34
27.700000 --> 27.7
55.000000 --> 55

Is there a function that will do this or do I need to write my own?


It's usually best to do this formatting in the client, rather than the
database layer - client tools will have much better formatting options, and
they allow for changing the formatting dynamically without having to
resubmit the query. But here's one way to do it:

select case
when right(replace(c onvert(varchar, MyColumn), '0', ''), 1) = '.'
then replace(replace (convert(varcha r, MyColumn), '0', ''), '.', '')
else replace(convert (varchar, MyColumn), '0', '')
end
from dbo.MyTable

Simon

Jul 20 '05 #4
Thanks for the help on this matter.

I agree with both of you. The formatting SHOULD be done on the client
when possible but in this case I have no choice. The logic will be
used in a function on the server when combining several fields to make
a new one.

Sh**********@ho tmail.com (Shervin Shapourian) wrote in message news:<4e******* *************** ****@posting.go ogle.com>...
Hi Jason,

I'm not sure if you can find a function in TSQL that does what you
want in this case. But you can use a bunch of string functions to do
the trick, like this:
select rtrim(
replace(
replace(
rtrim(
replace(
cast(DecField as char(11)),
'0',
' '
)
),
' ',
'0'
) + ' ',
'. ',
''
)
)
from YourTable

Let me explain what it does.
Assume you have these decimal values: 43.035000 and 55.000000

1. Convert decimal value into string
x = cast(DecField as char(11))
'43.035000 '
'55.000000 '

2. Replace 0's with space
x = replace(x, '0', ' ')
'43. 35 '
'55. '

3. Trim the trailing spaces
x = RTrim(x)
'43. 35'
'55.'

4. Replace spaces with 0
x = replace(x, ' ', '0')
'43.035'
'55.'

As you see by now all noninteger values are ready, but still we need
to get rid of decimal point appearing at the end of integer values.

5. Add a single space to the end
x = x + ' '
'43.035 '
'55. '

6. Replace '. ' with an empty string
x = replace(x, '. ', '')
'43.035 '
'55'

7. Finally get rid of remaining spaces
x = RTrim(x)
'43.035'
'55'
Well, this works fine but it's not the most efficient to do it.
Depending on size of your table and the number of decimal fields you
want to show, it might take a while. I personally prefer to perform
such cosmetic transformations in front-end application at the time you
display the values. Of course it depends on your programming
environment and its features.

Good luck,
Shervin

Jul 20 '05 #5
create table #t (c decimal(10,6))
insert into #t values (45.34)
insert into #t values (27.7)
insert into #t values (55)
insert into #t values (101.1)

select c,
replace(rtrim(r eplace(
replace(rtrim(r eplace(
cast(c as varchar(12))
,'0',' ')),' ','0')
,'.',' ')),' ','.')
from #t

drop table #t

Gert-Jan

Jason wrote:

I have a column defined as DECIMAL(10,6). I want to display it as a
string but I do not want the trailing zeros. I cannot seem to get CAST
or CONVERT or STR to exclude the zeros.

Examples:
45.340000 --> 45.34
27.700000 --> 27.7
55.000000 --> 55

Is there a function that will do this or do I need to write my own?

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
4580
by: Alberto Vera | last post by:
Hello: I have the next structure: How Can I make it using Python? How Can I update the value of 6?
23
3727
by: Hostile17 | last post by:
I keep coming across people, online and in real life, who believe that to code single tags like <br> and <img> with trailing slashes, <br /> and <img /> is considered "best practice" and when challenged, they say that "everyone's going to be using XML/XHTML soon" and say that they're "getting ready" by coding this way. Coding this way causes validation errors in HTML 4 Transitional and HTML 4 Strict, so it can't be "best practice". ...
5
3366
by: Stan Shankman | last post by:
How do I add trailing spaces within a right-justified textBox? In order to add a trailing space to the text in a right-justified textBox, I use to just append a space to the end of the text string. But now, textBox seems to have gotten smarter, as it automatically remove trailing spaces. So the question remains: How now do I add trailing spaces to text inside a right-justified textBox?
3
11201
by: V. Jenks | last post by:
I need to store money values in C# and I noticed when using a float, the trailing zero was trimmed off automatically. I don't want this, is there a native type that will *not* do this so I can have my trailing zeros? I want $0.10 not $0.1.....obviously that gives me serious data issues. Thanks!
0
1940
by: Seth | last post by:
First off, my apologies if this is in the wrong newsgroup, but I hope I'm close enough. I'm trying to do some parsing of a CSV file using OleDbConnection, but for some reason, when I populate my DataSet, it is trimming the trailing spaces. Anybody know why? Here is my code: System.Data.OleDb.OleDbConnection connection = null;
4
4377
by: Albert | last post by:
This isn't entirely related to C, but Kernighan and Ritchie asks in Execise 1-18 of their C programming language book to 'Write a program to remove trailing blanks and tabs from each line of input, and to delete entirely blank lines'. What do they mean by trailing?
17
11906
by: tommy | last post by:
Hi all, I' m adding strings to some fields in my table via Access. The strings sometimes have trailing spaces and I really need to have it that way, but Access truncates trailing spaces. How can I force Access not to truncate?! Thanx, /Toommy
9
8660
by: Chester | last post by:
I'm working on an app that records data collected by service technicians (VB.Net front-end, SQL Server 2000 back end). The technicians need to record numbers with varying scale and precision. For example, they may record one reading as 63.45 and the next as 123.1 and a third as 1.32456. That's fine - those can be saved as floating point numbers (very little math is done with these numbers so I'm not too worried about strange floating...
4
22358
by: lihao0129 | last post by:
Hi, folks: I recently went through a strange problem with my Javascript code, say: I have a string variable which are from a 'textarea' element and I want to remove the trailing newlines inside the string. I am using something like the following: var txt = textarea_element.value.replace(/\n*$/, ''); But this replaced only the last newline(by changing '' to 'K', and
5
2967
by: brian.j.parker | last post by:
Hey all, I've noticed an obscure little quirk: it appears that if you use a login with trailing spaces on the name, SYSTEM_USER automatically trims those trailing spaces in SQL Server 2000, but not SQL Server 2005. Anybody know if this change in behavior is documented? If it is intentional? Is there a "quick fix" to revert to the old behavior (to automatically RTRIM the results of SYSTEM_USER in 2005) until code can be changed?
0
9666
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10838
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10544
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10250
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9369
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7788
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6977
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4447
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.