Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Record Length Expand / Collapse
Author
Message
Posted Monday, August 31, 2009 11:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight 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 Boyer


You 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 FLOAT

SET @f = 25.300

SELECT count(@f)
WHERE cast((@f * 1000 /* that is for 2 decimals*/) AS INT)%10 = 0

SET @f = 25.301

SELECT 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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:01 PM
Points: 12,744, Visits: 31,074
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 Boyer


if 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.920000076293945
but that 151.00 = 151.000000000000000000000

create table #Example(exampleId int identity(1,1), ExampleTotal float)
insert into #Example
select 150.0 + (1.0 / RW) from (
select top 30 row_number() over (order by id) as RW,id from sysobjects
) x
--results
/*
151.000000000000000000000
150.500000000000000000000
150.333333333333333333333
150.250000000000000000000
150.200000000000000000000
150.166666666666666666666
150.142857142857142857142
150.125000000000000000000
150.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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 CastExample
FROM dbo.[Benefits.Benefits]

5 25.34 2009 0 25.34
0 26.92 2009 1 26.92
0 0 2009 1 0
0 0 2009 0 0
0 0 2009 0 0
0 0.9 2009 3 0.9
3 9.43 2009 6 9.43
0 26.92 2009 0 26.92
0 0.9 2009 6 0.9
0 0 2009 1 0
0 180.37 2009 6 180.37
0 0.9 2009 0 0.9
0 9.5799999237060547 2009 8 9.58
0 22.52 2009 6 22.52
0 0 2009 0 0
0 126.05999755859375 2009 0 126.06
0 0 2009 0 0
0 10.909999847412109 2009 8 10.91
0 0 2009 6 0
0 26.920000076293945 2009 8 26.92
0 2.809999942779541 2009 8 2.81
0 0 2009 0 0
0 5.61 2009 0 5.61
0 7.73 2009 1 7.73
0 26.92 2009 0 26.92
0 2.7000000476837158 2009 8 2.7
0 0 2009 3 0
3 3.42 2009 3 3.42
0 25 2009 3 25
0 0 2009 3 0
0 11.770000457763672 2009 8 11.77
0 126.06 2009 3 126.06
0 16.89 2009 3 16.89
0 2.81 2009 0 2.81
0 0 2009 0 0
0 19.5 2009 1 19.5
0 17.770000457763672 2009 0 17.77
0 80 2009 6 80
0 6.57 2009 0 6.57
0 0 2009 1 0
0 85 2009 0 85
3 9.4300003051757812 2009 0 9.43
0 0 2009 0 0
0 0 2009 3 0
0 0 2009 8 0
0 16.89 2009 0 16.89
0 0.9 2009 8 0.9
0 13.58 2009 3 13.58
0 0 2009 1 0
0 22.520000457763672 2009 0 22.52
0 0 2009 8 0
0 0 2009 8 0
2 24.55 2009 0 24.55
0 0 2009 6 0
0 0 2009 3 0
1 40 2009 0 40
0 0 2009 0 0
0 0 2009 8 0
NULL NULL NULL NULL NULL

Sorry 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

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight 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 YourTableName
WHERE 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 YourTableName
WHERE 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight 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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #780347
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse