none
sql query to format numbers

    Question


  • Hi all,

    Need assistance in building sql server query to format numbers as dont want to handle this formatting in front end.

    required format as under:
    format-number(@field,'###,##0')
    Tuesday, December 20, 2011 11:00 AM

Answers

  • Hi,

    Try with the below query.

    declare @VALUE VARCHAR(20)

    declare @LEN int

    declare @MARK int

    set @value = '1234567'

     

    IF ISNUMERIC(@VALUE) = 1

    BEGIN

    set @LEN = len(@value)

    set @mark = (@LEN/3)*3

    while @MARK > 0 and @VALUE is not null

    Begin

          IF @LEN <= 6 and @LEN >= 3

          BEGIN

                SELECT @value = STUFF(@VALUE, @LEN-@MARK+1, 0, ',')

                set @mark = @MARK - 4

          END

          IF @LEN > 6 and (@LEN%3) <> 0

          BEGIN

                SELECT @value = STUFF(@VALUE, @MARK+1, 0, ',')

                set @mark = @MARK - 3

          END

          ELSE IF @LEN > 6 and (@LEN%3) = 0

          BEGIN

                SELECT @value = STUFF(@VALUE, @MARK-2, 0, ',')

                set @mark = @MARK - 3

          END

    END

     

    IF @LEN%3 = 0 and @VALUE is not null

    select @value = STUFF(@value,1,1,'')

     

    if @VALUE is not null

    select @value

    END

      


    Thanks, RajaSekhara Reddy . K

    Thursday, December 22, 2011 3:16 PM
  • The following pages are on similar topic:

    http://www.sqlusa.com/bestpractices2005/moneyformat/

    http://www.sqlusa.com/bestpractices2005/padleadingzeros/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Wednesday, December 28, 2011 1:49 PM

All replies

  • Hi, try please :

    select replace(cast(12.54 as decimal(10,3)),'.',',')
    

     

     

    Best regards
    Tuesday, December 20, 2011 11:11 AM
  • Nilesh

     

    Please try 

    DECLARE @v2 DECIMAL (36,10)
    SELECT @v2 = 13243543.56565656
    
    SELECT substring(CONVERT(VARCHAR,CONVERT(money,@v2),1),1,CHARINDEX('.',CONVERT(VARCHAR,CONVERT(money,@v2),1))-1)+'0'
    
    


    O/P

    13,243,5430

     

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Tuesday, December 20, 2011 11:13 AM
  • Hi,

     

    @VT:

    This is not giving me desired results

    e.g.

    296,4630 - correct

    51,0000 - Incorrect

    _______________________________________

    @Baddi : 

    your query is giving comma before last 3 digits only.

    22315,000 - Incorrect

    223,150,00 - Correct

     

     

    i need comma for every thousand digit.

    Tuesday, December 20, 2011 12:19 PM
  • Hi,

    Try this,

    SELECT CAST(CONVERT(varchar, CAST(12322456 AS money), 1) AS varchar)
    


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Tuesday, December 20, 2011 12:32 PM
  • Hi Asim,

     

    This gives me below result  which is incorrect. 

    12,322,456.00

     

    1) dont want decimals

    2) final result should be 123,224,56

     

    Tuesday, December 20, 2011 12:38 PM
  • Hi,

    Using LEFT function on final result.

    DECLARE @v DECIMAL(18,2) = 123222245554
    
    SELECT CAST(LEFT(CONVERT(varchar, CAST(@v AS money), 1), LEN(CONVERT(varchar, CAST(@v AS money), 1))-3) AS varchar) 
    
    Hope it helps.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Tuesday, December 20, 2011 12:46 PM
  • Try please :

    Create Function dbo.format_number(@N Decimal(37,18))
    Returns varchar(100)
    Begin
    declare @i int
    declare @j int
    declare @S varchar(100)
    Set @i=len(cast(FLOOR(@N) as varchar))/3
    Set @j=0
    Set @S=''
    While @j<@i
    Begin
    Set @S=@S+substring(cast(FLOOR(@N) as varchar),@j*3+1,3)+','
    Set @j=@j+1
    End
    if len(cast(FLOOR(@N) as varchar))%3<>0 
    Set @S=@S+substring(cast(FLOOR(@N) as varchar),@j*3+1,3)+'0'
    Else
    Set @S=STUFF(@S,len(@S),1,'')
     
    return @S
    End
    ---------------------------------------------------
    select dbo.format_number(1234567)
    --123,456,70
     
    

     

     

    Best regards
    Tuesday, December 20, 2011 1:16 PM
  • Hi Asim,

     

    This query gives below result for @v= 12322224555

    12,322,224,555

     

    which is not the expected result.

    Tuesday, December 20, 2011 1:48 PM
  • Hi Baddi,

     

    This query works for the number which are multiples of 3

    ie. for 123456 gives result as : 123,456 (correct

    but for 1234567 gives result as 123,456,70 (incorrect)

    here last digit is unnecessary.

     

    I really appreciate your efforts.

    Tuesday, December 20, 2011 2:07 PM
  •  

    ALTER Function dbo.format_number(@N Decimal(37,18))
    Returns varchar(100)
    Begin
    declare @i int
    declare @j int
    declare @S varchar(100)
    Set @i=len(cast(FLOOR(@N) as varchar))/3
    Set @j=0
    Set @S=''
    While @j<@i
    Begin
    Set @S=@S+substring(cast(FLOOR(@N) as varchar),@j*3+1,3)+','
    Set @j=@j+1
    End
    Set @S=STUFF(@S,len(@S),1,'')
    return @S
    End
    select dbo.format_number(1234567)
    --123,456
    

     

     

    Best regards
    Tuesday, December 20, 2011 2:11 PM
  • Hi Baddi,

    Requirement is to format entire number.

    this query fails for 

    --select dbo.format_number(12345678)

    Result:

    --123,456

    Expected Result:

    --123,456,78

     

     

    Tuesday, December 20, 2011 2:21 PM
  •  

    ALTER Function dbo.format_number(@N Decimal(37,18))
    Returns varchar(100)
    Begin
    declare @i int
    declare @j int
    declare @S varchar(100)
    Set @i=len(cast(FLOOR(@N) as varchar))/3
    Set @j=0
    Set @S=''
    While @j<@i
    Begin
    Set @S=@S+substring(cast(FLOOR(@N) as varchar),@j*3+1,3)+','
    Set @j=@j+1
    End
    if len(cast(FLOOR(@N) as varchar))%3<>0 
    Set @S=@S+substring(cast(FLOOR(@N) as varchar),@j*3+1,3)
    Else
    Set @S=STUFF(@S,len(@S),1,'')
    return @S
    End
    select dbo.format_number(12345678)
    --123,456,78
    

     

     

    Best regards
    Tuesday, December 20, 2011 2:24 PM
  •  

    For 1234 result should be 1,234  - at present : 123,4

    For 12345 result should be 12,345 - at present : 123,45

    For 123456 result should be 123,456

    For 1234567 result should be 123,456,7

     

     

    Tuesday, December 20, 2011 3:06 PM
  • What is logic behind this formatting.

    Is the last result correct ..

    if given 1234567.is it 1,234,567 or 123,456,7.

     


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Tuesday, December 20, 2011 3:16 PM
  • If you convert your number to money first, then money data type has the desired format

    999,999,999.99


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, December 20, 2011 3:20 PM
  • Naomi is correct.  The best solution is to cast the value into the money type which creates the format $$$.$$ and if you dont want the decimal values, you can strip off the decimal places.

    DECLARE @t table(
    val int
    );
    
    insert into @t values (1234),(12345),(123456),(1234567)
    
    select t.val, LEFT(x.FmtVal,LEN(x.FmtVal)-3)
    from @t t
    CROSS APPLY(SELECT convert(varchar(25),cast(val as money),1) AS FmtVal) as x
    

     


    http://jahaines.blogspot.com/
    Tuesday, December 20, 2011 3:31 PM
  • It should be like : 123,456,7
    Tuesday, December 20, 2011 3:59 PM
  • Hi Adam,

    This query returns below result for 1507570

    Result: 1,507,570

    Expected Result : 150,757,0

     

    Tuesday, December 20, 2011 4:21 PM
  • Here is one way to do this

    DECLARE @t table(
    val int
    );
    
    insert into @t values (1234),(12345),(123456),(1234567),(1234567890)
    
    select t.val, x.fmtval, 
    	CASE 
    		WHEN LEN(val) >=7  AND RIGHT(val,1) = '0' THEN SUBSTRING(x.FmtVal,4,LEN(x.fmtVal)-4)
    		WHEN LEN(val) >=7  AND RIGHT(val,1) <> '0' THEN SUBSTRING(x.FmtVal,4,LEN(x.fmtVal))
    	ELSE LEFT(x.FmtVal,LEN(x.FmtVal)-3)
    	END
    from @t t
    CROSS APPLY(
    	SELECT
    		CASE 
    			WHEN LEN(VAL) >= 7 AND RIGHT(val,1) = '0'
    				THEN reverse(convert(varchar(30),cast('1' + reverse(cast(val as varchar(30))) AS money),1))
    			WHEN LEN(VAL) >= 7 AND RIGHT(val,1) <> '0'
    				THEN reverse(convert(varchar(30),cast(reverse(cast(val as varchar(30))) AS money),1))
    		ELSE convert(varchar(30),cast(val as money),1) 
    		END AS FmtVal
    ) as x
    
    

     


    http://jahaines.blogspot.com/
    Tuesday, December 20, 2011 4:58 PM
  • ALTER Function dbo.format_number(@N Decimal(37,18))
    Returns varchar(100)
    Begin
    declare @i int
    declare @j int
    declare @S varchar(100)
    Set @i=len(cast(FLOOR(@N) as varchar))/3
    Set @j=0
    Set @S=''
    If len(cast(FLOOR(@N) as varchar))<=3
    Set @S=cast(FLOOR(@N) as varchar)
    Else
    If len(cast(FLOOR(@N) as varchar))<6
    Set @S=Left(cast(FLOOR(@N) as varchar),len(cast(FLOOR(@N) as varchar))-3)+','+Right(cast(FLOOR(@N) as varchar),3)
    Else
    Begin
    While @j<@i
    Begin
    Set @S=@S+substring(cast(FLOOR(@N) as varchar),@j*3+1,3)+','
    Set @j=@j+1
    End
    if len(cast(FLOOR(@N) as varchar))%3<>0 
    Set @S=@S+substring(cast(FLOOR(@N) as varchar),@j*3+1,3)
    Else
    Set @S=STUFF(@S,len(@S),1,'')
    End
    return @S
    End
    select dbo.format_number(1234)
    --1,234
    select dbo.format_number(12345)
    --12,345
    select dbo.format_number(123456)
    --123,456
    select dbo.format_number(1234567)
    --123,456,7
    

     

     

    Best regards
    Tuesday, December 20, 2011 5:01 PM
  • How would you represent that using format as per your first post?  If possible then this is starting point

    DECLARE @format varchar(20)= '###,###,##0.00'
    
    declare @VALUE VARCHAR(20) = '123456789'
    
    DECLARE @TO_val INT = LEN(@VALUE)
    declare @to_format int = len(@format)
    DECLARE @FROM INT = 1
    
    declare @RESULT VARCHAR(210) = reverse(@value)
    declare @ISDIGIT BIT = 0
    While @FROM <= @to_format 
    begin
    	DECLARE @newchar char(1)
    	select @RESULT = case SUBSTRING(reverse(@format),@from,1) 
    		when '0' then @RESULT
    		when '.' then STUFF(@RESULT,@FROM,0,'.')
    		when ',' then STUFF(@RESULT,@FROM,0,',')
    		when '#' then @RESULT
    	end
    	set @FROM += 1
    end
    select reverse(@RESULT)
    
    


    Tuesday, December 20, 2011 5:06 PM
  • Hi Badii,

     

    This works as expected.

    But I'm not sure if it can handle 'null' values.

    Thursday, December 22, 2011 12:50 PM
  • Hi Badii,

     

    This works as expected.

    But I'm not sure if it can handle 'null' values.

     

    Yes, it can, try please :

    select

    dbo.format_number(NULL)
     

    --NULL

     


    Best regards
    Thursday, December 22, 2011 1:04 PM
  • It gives me below error:

    Incorrect syntax near the keyword 'NULL'.

     

    Is there any way to handle Null or Blank Value.

    Thursday, December 22, 2011 1:49 PM
  • Hi,

    Try with the below query.

    declare @VALUE VARCHAR(20)

    declare @LEN int

    declare @MARK int

    set @value = '1234567'

     

    IF ISNUMERIC(@VALUE) = 1

    BEGIN

    set @LEN = len(@value)

    set @mark = (@LEN/3)*3

    while @MARK > 0 and @VALUE is not null

    Begin

          IF @LEN <= 6 and @LEN >= 3

          BEGIN

                SELECT @value = STUFF(@VALUE, @LEN-@MARK+1, 0, ',')

                set @mark = @MARK - 4

          END

          IF @LEN > 6 and (@LEN%3) <> 0

          BEGIN

                SELECT @value = STUFF(@VALUE, @MARK+1, 0, ',')

                set @mark = @MARK - 3

          END

          ELSE IF @LEN > 6 and (@LEN%3) = 0

          BEGIN

                SELECT @value = STUFF(@VALUE, @MARK-2, 0, ',')

                set @mark = @MARK - 3

          END

    END

     

    IF @LEN%3 = 0 and @VALUE is not null

    select @value = STUFF(@value,1,1,'')

     

    if @VALUE is not null

    select @value

    END

      


    Thanks, RajaSekhara Reddy . K

    Thursday, December 22, 2011 3:16 PM
  • Function suggested by Mr. Baddi works correct to some extent but it is not handling blank or Null values.
    Wednesday, December 28, 2011 9:59 AM
  •  

    Hello Nilesh, to perform my function, can you post what's the ouptup for NULL and blank ?


    Best regards
    Wednesday, December 28, 2011 12:37 PM
  • The following pages are on similar topic:

    http://www.sqlusa.com/bestpractices2005/moneyformat/

    http://www.sqlusa.com/bestpractices2005/padleadingzeros/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Wednesday, December 28, 2011 1:49 PM
  • Hi Nilesh,

    Try with the below query.

    declare @VALUE VARCHAR(20)

    declare @LEN int

    declare @MARK int

    set @value = '1234567'

     

    IF ISNUMERIC(@VALUE) = 1

    BEGIN

    set @LEN = len(@value)

    set @mark = (@LEN/3)*3

    while @MARK > 0 and @VALUE is not null

    Begin

          IF @LEN <= 6 and @LEN >= 3

          BEGIN

                SELECT @value = STUFF(@VALUE, @LEN-@MARK+1, 0, ',')

                set @mark = @MARK - 4

          END

          IF @LEN > 6 and (@LEN%3) <> 0

          BEGIN

                SELECT @value = STUFF(@VALUE, @MARK+1, 0, ',')

                set @mark = @MARK - 3

          END

          ELSE IF @LEN > 6 and (@LEN%3) = 0

          BEGIN

                SELECT @value = STUFF(@VALUE, @MARK-2, 0, ',')

                set @mark = @MARK - 3

          END

    END

     

    IF @LEN%3 = 0 and @VALUE is not null

    select @value = STUFF(@value,1,1,'')

     

    if @VALUE is not null

    select @value

    END


    Thanks, RajaSekhara Reddy . K
    Wednesday, December 28, 2011 5:11 PM
  • It gives me below error:

    Incorrect syntax near the keyword 'NULL'.

    Thursday, December 29, 2011 7:32 AM