468,272 Members | 2,001 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

How do I format an integer

How do I format an integer. Add commas.

1234565 1,234,565

TIA
Jul 23 '05 #1
3 18950
stevek wrote:
How do I format an integer. Add commas.

1234565 1,234,565

TIA


Hi SteveK,

Create the following function

**************** START HERE ****************
CREATE FUNCTION [dbo].[FormatNumber] (

@number decimal(38,15),
@decimalplaces int=0,
@format varchar(115)='',
@ifzero varchar(115)=''
) RETURNS varchar(256)
AS BEGIN
/*
Valid @Format arguments (space between args is ignored)
nothing - returns the number unformatted
$ - return the number preceded by a '$' sign
% - return the number followed by a '%' sign
, - place a , every 3 zeros in the whole number portion (thousands)
c - divide the number by 100 - intended to calc percent values
i - returns integer portion only with no formatting except commas if
requested
d - returns the decimal portion only with no formatting except commas
if requested
b - returns a blank string for 0 values
( - encloses negative numbers in brackets
l - use leading zero
r[int]r - rounds number outside of the decimal context
z[int]z - zero fills to [int] width
*/

DECLARE @fmtxt varchar(25), @parsetxt varchar(50)
, @parsetxtdec varchar(50)
, @decptloc int, @zerotext varchar(100)
, @intpart varchar(25), @decpart varchar(25)
, @ERR_type varchar(15), @roundto varchar(2)
, @fillto varchar(50), @fillto# varchar(2)

--A little error checking is in order
IF @number IS NULL
RETURN '{ERR-null passed}'
ELSE IF @decimalplaces < 0
RETURN '{ERR-decimal spec <0}'
ELSE IF @decimalplaces >15
RETURN '{ERR-decimal spec >15}'

-- Handle zero values first
IF @number = 0 RETURN @ifzero

-- Now 'C'alculate the percentage if requested using the '%c' arg.
IF CHARINDEX('%c',@FORMAT) > 0 SET @number = @number * 100

-- Do rounding outside if applicable
IF CHARINDEX('r',@FORMAT) > 0 BEGIN
SET @roundto = SUBSTRING(@FORMAT,CHARINDEX('r', @FORMAT)+1, 115)
SET @roundto = LEFT(@roundto,CHARINDEX('r',@roundto)-1)
SET @number = round(@number,cast(@roundto as integer))
END

-- Get the parsetext variable
IF CHARINDEX(',',@FORMAT) > 0
SET @parsetxt = CONVERT(varchar(100),CAST(@number as money),1)
ELSE
SET @parsetxt = CONVERT(varchar(100), @number)

-- Grab some basic stuff
SET @decptloc = ISNULL(CHARINDEX('.',@parsetxt),0)

IF @decptloc = 0
RETURN @parsetxt
ELSE
SET @intpart = SUBSTRING(@parsetxt,1,@decptloc-1)

-- Handle leading zeros
IF CHARINDEX('l',@FORMAT) = 0 AND @intpart = '0' SET @intpart = ''

-- Now build the decimal portion of the result
SET @parsetxt = CONVERT(varchar(100),ROUND(@number,@decimalplaces) ,2)
SET @decptloc = ISNULL(CHARINDEX('.',@parsetxt),0)
IF @decimalplaces = 0
SET @decpart = ''
ELSE
SET @decpart = LEFT(SUBSTRING(@parsetxt
+ REPLICATE('0',@decimalplaces)
,@decptloc
,@decptloc+50)
,@decimalplaces+1)

--ASSEMBLE THE RESULTS --

-- for just integer portion
IF CHARINDEX('i',@FORMAT) > 0
RETURN @intpart
-- for just decimal portion
IF CHARINDEX('d',@FORMAT) > 0
RETURN + @decpart

SET @fmtxt = @intpart + @decpart
--SET @fmtxt = @intpart +'*'+ @decpart

-- Handle brackets if requested
IF CHARINDEX('(',@FORMAT) > 0 AND @number < 0
SET @fmtxt = '(' + RIGHT(@fmtxt,LEN(@fmtxt)-1) + ')'

-- Add the symbols
IF CHARINDEX('$',@FORMAT) > 0
SET @fmtxt = '$' + @fmtxt
ELSE IF CHARINDEX('%',@FORMAT) > 0
SET @fmtxt = @fmtxt + '%'

--Handle zero filling
IF CHARINDEX('z',@FORMAT) > 0 BEGIN
SET @fillto = SUBSTRING(@FORMAT,CHARINDEX('z',@FORMAT)+1,115)
SET @fillto# = CAST(LEFT(@fillto,CHARINDEX('z',@fillto)-1) as INT)
SET @fmtxt = RIGHT(REPLICATE('0',@fillto#) + @fmtxt,@fillto#)
END

RETURN @fmtxt

END
GO

**************** END HERE ****************

Now we need to grant access to it:

GRANT EXEC on dbo.FormatNumber to PUBLIC
GO

and to use it do the following:

declare @testnum int
SET @TestNum = 123456789

select dbo.FormatNumber (@TestNum, '0', ',', 'zero') as [Integer]

Good luck

Michael van der Veeke
michaelvanderveeke AT borderexpress DOT com DOT au
Jul 23 '05 #2
Formatting and presentation usually belong in the client application,
not the server. The formatting above is not correct in many European
languages, for example - by doing it on the client you can use the
correct locale information for different clients, or use formatting
masks/functions like printf or whatever your preferred development
language has.

Simon

Jul 23 '05 #3
I agree with Simon that data formatting is best performed on in the
presentation layer rather than in SQL. That said, you can use CONVERT to
add commas and string functions to remove extraneous characters.

SELECT
REVERSE(SUBSTRING(REVERSE(CONVERT(varchar(20), CAST(1234565 AS money),
1)), 4, 20))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"stevek" <so*****@somewhere.org> wrote in message
news:IV*********************@bgtnsc05-news.ops.worldnet.att.net...
How do I format an integer. Add commas.

1234565 1,234,565

TIA

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Josiah Carlson | last post: by
6 posts views Thread by J | last post: by
16 posts views Thread by Al Reid | last post: by
20 posts views Thread by andreas | last post: by
11 posts views Thread by RipperT | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.