## Record Length

 Author Message gboyer SSC Veteran Group: General Forum Members Points: 208 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 J-F Bergeron SSCarpal Tunnel Group: General Forum Members Points: 4545 Visits: 2707 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 Lowell SSC Guru Group: General Forum Members Points: 185373 Visits: 41569 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--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! gboyer SSC Veteran Group: General Forum Members Points: 208 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 J-F Bergeron SSCarpal Tunnel Group: General Forum Members Points: 4545 Visits: 2707 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 gboyer SSC Veteran Group: General Forum Members Points: 208 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. J-F Bergeron SSCarpal Tunnel Group: General Forum Members Points: 4545 Visits: 2707 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 gboyer SSC Veteran Group: General Forum Members Points: 208 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. J-F Bergeron SSCarpal Tunnel Group: General Forum Members Points: 4545 Visits: 2707 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 Jeff Moden SSC Guru Group: General Forum Members Points: 513245 Visits: 44331 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs