Post reply

Using SQL to CONVERT an int with Thousand Seperator!

  • January 4, 2005 at 9:54 am

    #361807

    I need some assistance with this SQL statement:

    SELECT (CONVERT(varchar, CAST(SUM(S526960.tappMonth2Date.Offered) AS money),1)) AS [Offered]

    I need the result to be like 15,345 not like 15,345.00...

    Am I in the right forum for this question...I hope so, I am new here as well as to SQL in SQL Server 2000.

    Thank You.

  • January 4, 2005 at 10:15 am

    #535812

    Add a ROUND()?

    SELECT cast( round((CONVERT(varchar, CAST(SUM(val) AS money),1)),0) as int) AS [Offered]

    from mytable

  • January 4, 2005 at 10:37 am

    #535816

    That makes perfect sense, yet I probably should have stated that this SQL statement is within a JSP page, and is connected to the SQL Server by way of the JDBC connection.

    With that being said...I am not getting any input at all now...

    Here is the code that I have:

    CAST(ROUND(CONVERT(varchar, CAST(SUM(val) AS money),1),0) AS int) AS [Offered]

    Would there be some differences with the placement of the parenthases when placing the SQl on a JSP?

    Thank you very much!!!

  • January 4, 2005 at 10:43 am

    #535817

    I got an error with the round:  Error converting data type varchar to float.

    I hacked some code together that seems to work:

    SELECT substring(convert(varchar,cast(sum([column]) as money),1),1,charindex('.',convert(varchar,cast(sum([column]) as money),1))-1)

    from

    ..not sure about issues on your JSP.

  • January 4, 2005 at 11:01 am

    #535824

    Osoba,

    Thank you very much...your hack code worked great...

    Would you mind passing me some info on the substring, and why you came to this conclusion...it will definitely assist me in understanding SQL a little better.

    Thank you again!

  • January 4, 2005 at 11:15 am

    #535832

    My thought process was this:

    Here is your returned number 15,345.00 which is a converted money datatype.  The round won't work because its not a numeric datatype and once you convert to another numeric datatype you lose the ',' separator.

    Substring allows you to select from an expression by giving the arguments: expression(the selected and converted string), start <position on the string to begin selecting>, and length <of how many characters on the string to choose>

    I used the Charindex function to give me the length of the string from beginning (postion starting from left to right) to end.  I specified the end of the string to be where the character '.' existed.

    ..hope that helps make sense of the string I submitted.

  • January 5, 2005 at 2:11 am

    #535916

    May I ask, why you want to handle this on the server?

    I guess most people here, consider this merely a presentational issue that should be handled at the client.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 5, 2005 at 8:25 am

    #535998

    I don't know if it would run any faster (or slower), but if you always want to truncate the right-most 3 characters wouldn't it be easier to use

    SELECT (left(CONVERT(varchar, CAST(SUM(S526960.tappMonth2Date.Offered) AS money),1),len(CONVERT(varchar, CAST(SUM(S526960.tappMonth2Date.Offered) AS money),1)-3) AS [Offered]

    This assumes a "...nnn,nnn.xx" format and just chops the last 3 characters. I don't know if using a LEFT and LEN would work any better than SUBSTRING and CHARINDEX...

    Probably two equally worthwhile solutions, eh?

     

Viewing 8 posts - 1 through 7 (of 7 total)

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