Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2009
    Posts
    16

    Question Unanswered: truncating numbers returned in a query

    Hi,
    direction is appreciated:

    my query returns many columns with numbers and text, but the numbers come back with 12.12340000000 or something like that, I would like to keep the significant accuracy, XX.XXXX, but drop the extra meaningless 0's.

    suggestions?

    thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Code:
    select convert(numeric(6, 4), 12.1234556)
    but formatting should really be done at the display end of things.

  3. #3
    Join Date
    Apr 2009
    Posts
    16

    Thank you!

    thanks, that really rings a bell, that should do the job, i will try it.

    I may not know where to put the code exactly. My query looks something like this

    Select aas,ddgf,23rf,ast,sdf, from asdfgggh where asdf>=@asdF AND
    AND ([Ff] >= @FF) AND (CID LIKE '%@C_ID%')

    Two things #1 i tried to insert the convert (numeric(6,4) , ast) is that the correct usage? So I would have to put it in after every column name?
    #2 my (CID LIKE '%@C_ID%') doesn't work, the C_ID is a textbox in VisualStudio2008, ([Ff] >= @FF) is also from a text box to create the query, but the CID doesn't work like I thought it should, does not return anything.

    suggestions?
    thanks
    Last edited by RobSimons; 04-14-09 at 15:44.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    if @C_ID is a variable, the like is reading that as a literal, not the variable value.

    have to build sql string ie

    set @SQL = 'select * from tablename where cola like ''' + '%' + isnull(@C_ID,'') + '%'''
    Last edited by PMASchmed; 04-14-09 at 18:11.

  5. #5
    Join Date
    Apr 2009
    Posts
    16

    Ok now?

    it is a variable tied to a text box. How do I make it see it as the value? I think it's possible, altho I cannot tell now how to do it.

    thanks

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    PMASchmed, you don't have to go that far with the dynamic-ness!
    Code:
    SELECT *
    FROM   tablename
    WHERE  cola LIKE '%' + @some_variable + '%'
    Should work fine
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2009
    Posts
    16
    thanks, again familiar territory, just details i'm not getting yet!

    that should work like a charm.

  8. #8
    Join Date
    Apr 2009
    Posts
    16
    troubles continue with my project:

    Arithmetic overflow error converting numeric to data type numeric.
    is the message that comes up in the web page where i used several
    Code:
    convert (numeric (5, 2) variable) AS cup

    I am sure the data coming back is numeric, in fact may be float, is that an issue with the convert?

    Sorry, tried several things now it is oK. I didin't have a large enough first number where 5 is in my example. made it 7 and bingo...

    //////now is MID a known function? on one W3 school it says it is, but when i try to use it i get errors stating it isn't a known function... what did i miss?
    thanks
    Last edited by RobSimons; 04-15-09 at 16:44.

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by gvee
    PMASchmed, you don't have to go that far with the dynamic-ness!
    Code:
    SELECT *
    FROM   tablename
    WHERE  cola LIKE '%' + @some_variable + '%'
    Should work fine

    Oh, okay, I thought I tried this in the past and it did not work, thanks.

  10. #10
    Join Date
    Apr 2009
    Posts
    16
    this code you just re-iterated does work. "cola LIKE '%' + @some_variable + '%'

    I have moved on to a new problem, trying to "parse" the text string that comes back from a column, and add the columns needed to the data grid view. I thought (from a web SQL function page) MID is a function that is known to MS SQL, apparently I am mistaken.

    other thoughts on "parsing" a text string read from a column in MS SQL server 2005?

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The equivalent function in SQL Server is SubString()
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2009
    Posts
    16
    Ok, I'll give that a try... thanks!

  13. #13
    Join Date
    Apr 2009
    Posts
    16
    Tried this:
    code;

    SELECT CellID,Device_ID
    substring(Device_ID,1,6)as Device_Date

    Where CellID, and Device_ID are columns. I get incorrect syntax at Device_ID.

    suggestions? thanks.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You appear to be missing a comma after Device_ID.

  15. #15
    Join Date
    Apr 2009
    Posts
    16
    worked like a champ, George, Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •