7 places after the (.) point.

Question

7 places after the (.) point.

neotokyo

SSCertifiable

Points: 7239
More actions
November 1, 2007 at 10:03 am

#122186

i've got a column with a bunch of 'decimal' values such as the following:
column: MyNumbers
1234.000202001
13.022038503
2345.03075654
340.48993
3.023098
i'm looking for values which have at least 7 places after the decimal(.) point.
i've tried left and right functions, and because the values vary in decimal poisition
it's not working. is there another way i can do this?
_________________________

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Meierruth SSChampion Points: 10051 More actions · Answer 1

I'm not sure that's going to be easy. Thus I did a quick display of numbers in a float column where I know I placed numbers with at most 6 decimal places. Yet look what it shows me.

3.8395049999999999

3.2936740000000002

3.8241399999999999

4.8120260000000004

3.9502869999999999

4.3217679999999996

3.6877260000000001

3.9414030000000002

3.7448090000000001

How did you get your numbers to display the way you show them?

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 2

What data type is the column?

If its a char or varchar you could do the following

Charindex('.',REVERSE(@test))-1> 7

I was trying to figure out how to handle it if its a decimal but any time I added enough precision to test I got trailing zero's so this test doesn't work.

You could use the following test for a numeric data type.

CASE WHEN ROUND(@Test,6) <> @test-2 THEN 'Yes' ELSE 'No' END

The ROUND function will round it to 6 decimal places. If there is any non zero after that it won't match the orriginal value. If there is a non zero value after the 6th place it will no longer match.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 3

i was just about to post the same thing as michael did...if the table has definitions for decimal 20,10) for example, it keeps 10 characters...you really want 7 siginificicant digitis, which means trimming off trailing zeros as well.

declare @MyTable TABLE(MyValue Decimal (20,10))

INSERT INTO @MyTable(MyValue)

SELECT 1234.000202001

UNION SELECT 13.022038503

UNION SELECT 2345.03075654

UNION SELECT 340.48993

UNION SELECT 3.023098

--cheasy way: convert ot varchar and use len() of a substring:

select MyValue,convert(varchar(30),MyValue) from @MyTable

3.02309800003.0230980000

13.022038503013.0220385030

340.4899300000340.4899300000

1234.00020200101234.0002020010

2345.03075654002345.0307565400

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!

neotokyo SSCertifiable Points: 7239 More actions · Answer 4

thanks for all the response ! 🙂

it is a decimal datatype.

the numbers shown are simply an example to describe

the differing locations of the decimal.

let me go ahead, and try some of these.

thanks again!

_________________________