Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Record Length Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, August 31, 2009 11:59 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, November 23, 2009 6:59 AM Points: 42, Visits: 46
 All,I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.I know there has to be a way but alas it has eluded me. Please assist.TIA,Gabriel Boyer
Post #780106
 Posted Monday, August 31, 2009 12:12 PM
 Right there with Babe Group: General Forum Members Last Login: Thursday, September 19, 2013 6:00 AM Points: 715, Visits: 2,706
 gboyer (8/31/2009)All,I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.I know there has to be a way but alas it has eluded me. Please assist.TIA,Gabriel BoyerYou could use the modulo operator, you first need to multiply your float by whichever number of decimals you want to check, cast as integer, and check the last digit with modulo 10.Here's how:`DECLARE @f FLOATSET @f = 25.300SELECT count(@f)WHERE cast((@f * 1000 /* that is for 2 decimals*/) AS INT)%10 = 0SET @f = 25.301SELECT count(@f)WHERE cast((@f * 1000) /* that is for 2 decimals*/ AS INT)%10 = 0`Tell me if that helps, Cheers,J-F
Post #780117
 Posted Monday, August 31, 2009 12:15 PM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 2:42 PM Points: 12,276, Visits: 29,444
 gboyer (8/31/2009)All,I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.I know there has to be a way but alas it has eluded me. Please assist.TIA,Gabriel Boyerif you convert a float to a string, there is always going to be more than three chars after the decimal point. check out the results of my example table, you get values like 151.000000000000000000000 for the conversion when it rounds nice an pretty....so it wouldn't help to convert to a string.i think the thing to do is to forget casting to string,a nd jsut find items that are not equal to the round to two decimal palces of the number....that would tell you that 26.92 <> 26.920000076293945but that 151.00 = 151.000000000000000000000 `create table #Example(exampleId int identity(1,1), ExampleTotal float)insert into #Exampleselect 150.0 + (1.0 / RW) from (select top 30 row_number() over (order by id) as RW,id from sysobjects) x--results /*151.000000000000000000000150.500000000000000000000150.333333333333333333333150.250000000000000000000150.200000000000000000000150.166666666666666666666150.142857142857142857142150.125000000000000000000150.111111111111111111111*/select * from #Example where round(ExampleTotal,2) <> ExampleTotal --two decimal places` Lowell--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #780121
 Posted Monday, August 31, 2009 12:31 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, November 23, 2009 6:59 AM Points: 42, Visits: 46
 Here is an example of my results with my T-Sql:SELECT CHARINDEX('4', Amount, 1) AS Length, Amount, EnrollmentYear, CHARINDEX('4', SSN, 1) AS Expr1, CAST(Amount AS varchar(50)) AS CastExampleFROM dbo.[Benefits.Benefits]5 25.34 2009 0 25.340 26.92 2009 1 26.920 0 2009 1 00 0 2009 0 00 0 2009 0 00 0.9 2009 3 0.93 9.43 2009 6 9.430 26.92 2009 0 26.920 0.9 2009 6 0.90 0 2009 1 00 180.37 2009 6 180.370 0.9 2009 0 0.90 9.5799999237060547 2009 8 9.580 22.52 2009 6 22.520 0 2009 0 00 126.05999755859375 2009 0 126.060 0 2009 0 00 10.909999847412109 2009 8 10.910 0 2009 6 00 26.920000076293945 2009 8 26.920 2.809999942779541 2009 8 2.810 0 2009 0 00 5.61 2009 0 5.610 7.73 2009 1 7.730 26.92 2009 0 26.920 2.7000000476837158 2009 8 2.70 0 2009 3 03 3.42 2009 3 3.420 25 2009 3 250 0 2009 3 00 11.770000457763672 2009 8 11.770 126.06 2009 3 126.060 16.89 2009 3 16.890 2.81 2009 0 2.810 0 2009 0 00 19.5 2009 1 19.50 17.770000457763672 2009 0 17.770 80 2009 6 800 6.57 2009 0 6.570 0 2009 1 00 85 2009 0 853 9.4300003051757812 2009 0 9.430 0 2009 0 00 0 2009 3 00 0 2009 8 00 16.89 2009 0 16.890 0.9 2009 8 0.90 13.58 2009 3 13.580 0 2009 1 00 22.520000457763672 2009 0 22.520 0 2009 8 00 0 2009 8 02 24.55 2009 0 24.550 0 2009 6 00 0 2009 3 01 40 2009 0 400 0 2009 0 00 0 2009 8 0NULL NULL NULL NULL NULLSorry for the mess.I tried to do a cast to varchar and it rounded the result
Post #780143
 Posted Monday, August 31, 2009 1:04 PM
 Right there with Babe Group: General Forum Members Last Login: Thursday, September 19, 2013 6:00 AM Points: 715, Visits: 2,706
 Have you even read the solutions we posted to your problem, before posting that data?Both solutions will solve the problem you have actually with the float values. If you have problems understanding the way it works, please ask questions. Cheers,J-F
Post #780166
 Posted Monday, August 31, 2009 1:16 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, November 23, 2009 6:59 AM Points: 42, Visits: 46
 You're right. I am sorry. I have a tough admitting I do not understand something. With your solution, J-F Bergeron, I have no idea how to adapt it to my situation. And with not understanding I have no idea even where to begin to ask questions.
Post #780177
 Posted Monday, August 31, 2009 1:29 PM
 Right there with Babe Group: General Forum Members Last Login: Thursday, September 19, 2013 6:00 AM Points: 715, Visits: 2,706
 Well, if that is the case, I will explain to you how you could implement it.You said you wanted to count the number of occurences that had more than 2 decimals.Here's how:First Method : The modulo method.`SELECT count(* )FROM YourTableNameWHERE cast((FloatColumnName * 1000) /* that is for 2 decimals*/ AS INT)%10 = 0`Second Method, compare the rounding of the value with the value itself, to see if there was any more decimals`SELECT count(* )FROM YourTableNameWHERE round(FloatColumnName,2) <> FloatColumnName`Just put in your table name, and your column name, and it will give you the count you were expecting.If you need more assistance, please feel free to post your question. Cheers,J-F
Post #780186
 Posted Monday, August 31, 2009 1:33 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, November 23, 2009 6:59 AM Points: 42, Visits: 46
 J-F,Thanks for the push. I went and looked again at the post from Lowell and with you help was able to move forward. Thanks again. Sorry lack of confidence is showing.
Post #780194
 Posted Monday, August 31, 2009 1:56 PM
 Right there with Babe Group: General Forum Members Last Login: Thursday, September 19, 2013 6:00 AM Points: 715, Visits: 2,706
 Happy it helped,and don't worry, we all have to start somewhere, just ask questions if you are not sure. But playing with the supplied code is always a good way to learn.Have a nice day, Cheers,J-F
Post #780218
 Posted Monday, August 31, 2009 5:17 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 9:51 PM Points: 34,581, Visits: 28,764
 gboyer,Since you're brand new to the forum, you might want to take a look at the article at the following URL...http://www.sqlservercentral.com/articles/Best+Practices/61537/If you follow those steps (yep... it takes just a bit of time extra, but not much), I guarantee that you'll get better answers quicker for your future posts even if you're not 100% sure which question to ask.And, welcome aboard... --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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #780347

 Permissions