Solved

Trim decimal places from Varchar field

Posted on 2013-10-31
7
364 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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

17 Experts available now in Live!

Get 1:1 Help Now