SQL Server Format Decimal Places with Commas

SQL Server format decimal places with commas

without considering this to be a good idea...

select dbo.F_AddThousandSeparators(convert(varchar, convert(decimal(18, 4), 1234.1234567), 1))

Function

-- Author:      bummi
-- Create date: 20121106
CREATE FUNCTION F_AddThousandSeparators(@NumStr varchar(50)) 
RETURNS Varchar(50)
AS
BEGIN
declare @OutStr varchar(50)
declare @i int
declare @run int

Select @i=CHARINDEX('.',@NumStr)
if @i=0 
    begin
    set @i=LEN(@NumStr)
    Set @Outstr=''
    end
else
    begin   
     Set @Outstr=SUBSTRING(@NUmStr,@i,50)
     Set @i=@i -1
    end 

Set @run=0

While @i>0
    begin
      if @Run=3
        begin
          Set @Outstr=','+@Outstr
          Set @run=0
        end
      Set @Outstr=SUBSTRING(@NumStr,@i,1) +@Outstr  
      Set @i=@i-1
      Set @run=@run + 1     
    end

    RETURN @OutStr

END
GO

How do I format a number with commas in T-SQL?

In SQL Server 2012 and higher, this will format a number with commas:

select format([Number], 'N0')

You can also change 0 to the number of decimal places you want.

Show comma instead of point as decimal separator

Well, as far as I know, there are no culture-specific options for convert available.

So you can do it using replaces (yes, it looks a bit ugly...)

select 
    replace(replace(replace(convert(varchar(30), @euros, 1), ',', '|'), '.', ','), '|', '.')

Idea: first change comma to something, then change dot to comma, and then "something" back to dot.

Format a number with commas but without decimals in SQL Server 2008 R2?

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT  left(@val, len(@val) - 3)

This also works with digits after the decimal point:

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT  left(@val, len(@val) - 3)

Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.

How to display two digits after decimal point in SQL Server

select cast(your_float_column as decimal(10,2))
from your_table

decimal(10,2) means you can have a decimal number with a maximal total precision of 10 digits. 2 of them after the decimal point and 8 before.

The biggest possible number would be 99999999.99

SQL Server: How can I format a float as a string, without commas?

You want to use Format function for this format

It can be done like this :

declare @num as float
set @num=21739130.43478260

select format(@num, N'#.##############################');
-- output : 21739130.4347826

There are many ways to do this:

Convert function :

SELECT CONVERT(varchar(100), Cast(@num as decimal(38, 2)))
--output : 21739130.43

SELECT CONVERT (VARCHAR(50), @num,128)
--output : 21739130.4347826090000000

String function :

SELECT LTRIM(STR(@num, 25, 99))
--output : 2.1739130434782609e+007

SQL - Show point instead of comma as decimal separator

SELECT REPLACE(SUBSTRING('2,642.11',1,CHARINDEX('.','2,642.11')-1),',','.')+REPLACE(SUBSTRING('2,642.11',CHARINDEX('.','2,642.11'),1000),'.',',') as AlinanFF

How to format a sql column with comma to number format

What a bad idea to store numbers in string columns. And then even store invalid numbers. Well, check the antepenultimate character. If it's a comma change it to a dot.

case when left(right(str, 3), 1) = ',' then
  stuff(str, len(str) - 2, 1, '.')
else
  str
end

Or if all numbers end with two decimals just:

stuff(str, len(str) - 2, 1, '.')

(Once you've repaired your numbers, you should put them in a numeric column and drop the existing text column.)

---

Related Topics