Solved

Trim decimal places from Varchar field

Posted on 2013-10-31
7
365 Views
Last Modified: 2013-10-31
Hi.. I have varchar field with values like this
00.1231556
123.99999999
56.12344455

Only need to 2 decimal place

00.12
123.99
56.123

what's the syntax ?
0
Comment
Question by:JElster
7 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39614325
The ROUND function would work.
ROUND(<value>, 2, 1)
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 110 total points
ID: 39614331
Sorry, forgot about the varchar.

So CAST(ROUND(CAST(<value> AS decimal), 2, 1) AS varchar)
0
 
LVL 1

Author Comment

by:JElster
ID: 39614337
Error converting data type varchar to float.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39614342
Another option would be to use CHARINDEX to find the position of the period, add 2 to that value and then use LEFT:

LEFT(<value>, CHARINDEX('.', <value>) + 2)
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 110 total points
ID: 39614345
Shaun_Kline is correct.   Slight correction though, adding percision and scale to the decimal
Declare @str varchar(10) = '123.456'

SELECT CAST(ROUND(CAST(@str AS decimal(5,2)), 2, 1) AS varchar) 

Open in new window

This does beg the obvious question though, why is a column with numeric values a varchar data type?
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 39614361
As you have posted this in javascript, I assume you want the javascript to pre-format for you and the code for that is:

var twoPlacedFloat = parseFloat(yourString).toFixed(2);

Cd&
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39614362
Based on the error you posted, I'm guessing you have non-numeric data (maybe empty strings or NULLs) in that field as well. You can use the ISNUMERIC function to filter out records that does not contain numbers in your field.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now