Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Record Length


Record Length

Author
Message
gboyer
gboyer
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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
J-F Bergeron
J-F Bergeron
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 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 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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
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

--
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
gboyer
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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
J-F Bergeron
J-F Bergeron
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 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
gboyer
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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.
J-F Bergeron
J-F Bergeron
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 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 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
gboyer
gboyer
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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.
J-F Bergeron
J-F Bergeron
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45420 Visits: 39942
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search