Post reply

7 places after the (.) point.

  • 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?

    _________________________

  • November 1, 2007 at 10:36 am

    #747461

    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?

  • November 1, 2007 at 10:39 am

    #747463

    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]

  • November 1, 2007 at 10:42 am

    #747465

    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.0230980000 3.0230980000

    13.0220385030 13.0220385030

    340.4899300000 340.4899300000

    1234.0002020010 1234.0002020010

    2345.0307565400 2345.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!

  • November 1, 2007 at 11:03 am

    #747472

    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!

    _________________________

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

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