How to format numbers directly by SQL

Asked

Viewed 2,093 times

1

Gentlemen, my numbers are coming out without formatting when I perform the select directly at the bank, they are coming out as follows:

REALIZADO             META
61274436,2090003    80000000,00

How can I make them come out legible to whoever’s playing them?

sql database sql-server query
  • 1

    Define readable for those who interpret them

  • I imagine that’s sql-server on account of the function datepart correct? What would be readable for you? Round?

  • @Leandroangelo in this case, I would like to insert the scores in their correct positions.

  • It’s Sql-server even @Ricardopunctual, I don’t want to round them up, just insert the scores in their proper places.

  • This should be done in your application through masks, otherwise you will no longer have numerical values, only texts. Where you display this information and what else you do with it after the query?

  • Like: "61.274.436,2090003"? But the user/you will read the direct result of sql or in an application (form, web page, etc)? Because the responsibility of formatting is the presentation, but if you don’t have it, only the result of the database is possible to format

  • If you use SQL Server 2012 or more current, has the function format: select FORMAT(61274436.2090003,'#,0.0000', 'pt-BR'), see if this is what you need that I put in a more detailed answer

  • That’s just what I need @Ricardopunctual.

Show 3 more comments

1 answer

2


Using from the SQL SERVER 2012, you can use the function FORMAT :

SELECT FORMAT(A.REALIZADO,'#,0.0000', 'pt-BR') ...

Using only the pattern '#0.0000' will format the display using punctuation, but will be displayed according to the setting SQL Server, which, if configured in English for example, will display "61,274,436,2090". To display in the correct format, use the last parameter, which is "culture", in the example, I used "en-BR" to display dots in the thousands and comma in the decimals.

Can be used with both numerical and date types, check the table on the link above with the documentation.

  • Ricardo, this query will be inserted in my Javascript, I tried to insert but he did not recognize, I was already using the query, but only implement this new functionality that my Javascript did not recognize, know if there is some other way to use it within my code?

  • In the javascript? We usually put this in the server-side code (c#, php, Java, etc), or you are using node-js?

  • This is the query I already use today inside my JSP and it works. <snk:query var="SG"> SELECT A.REALIZED, A.META, FLOOR((A.REALIZED/A.META)100) ACCUMULATED FROM ( SELECT ((SELECT ISNULL(SUM((ITE1.QTDNEGITE1.VLRUNIT)-ITE1.VLRDESC-ITE1.VLRREPRED),0).... After implementing this configuration that you informed me she no longer recognizes, only works in the bank...

  • strange because it should only pass the query to the bank, and if it works in the bank should also work in the code. Just note that if the result was previously a numerical value, like decimal or float, I’m not sure what kind of field your table, using format he will be now nvarchar, check if your code is treating the fields you are using format as nvarchar, otherwise it will give error in the query return

Browser other questions tagged sql database sql-server query

You are not signed in. Login or sign up in order to post.