Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 9th, 2007, 10:51 PM
Registered User
 
Join Date: Jun 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 12345 to 12,345 >how to format?

How to change the number 12345678 to 12,345,678?

Thank YOU.

 
Old August 13th, 2007, 05:19 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 114
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I couldn't see a proper way of masking the data.

Have a play with something like this...



CREATE FUNCTION fn_ADD_THOUSAND_SEPARATOR (@input_value NUMERIC) RETURNS VARCHAR(75) AS

BEGIN

    DECLARE @input VARCHAR(50)
    DECLARE @output VARCHAR(50)

    SELECT @input = convert(varchar, @input_value)
    SELECT @output = ''

    WHILE LEN(@input) > 2
    BEGIN
        SELECT @output = ',' + RIGHT(@input, 3) + @output
        SELECT @input = LEFT(@input, LEN(@input) - 3)
    END

    IF (LEN(@input) > 0)
    BEGIN
        SELECT @output = @input + @output
    END
    ELSE
    BEGIN
        SELECT @output = RIGHT(@output, LEN(@output) - 1)
    END

    RETURN @output

END

GO

SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(12)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(123)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1234)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(12345)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(123456)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1234567)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(12345678)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(123456789)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1234567890)




Regards,

Sean Anderson
 
Old August 13th, 2007, 05:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 184
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jonax
Default

First of all: This is not something that should be done at databaselevel - formatting belongs in the presentation-layer.

That being said - Sean's solution seems fine, except it won't handle floating point values.






Similar Threads
Thread Thread Starter Forum Replies Last Post
want to convert date from 01/12/07 to 12-Jan-2007 hurperl Perl 1 April 12th, 2007 05:47 AM
Chpt 7 >> Pg 245 >>Try It Out #4-5 harrison4411 BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 0 March 2nd, 2006 06:26 PM
Html > csv format goldspider Beginning PHP 2 March 27th, 2005 06:39 PM
Ch. 12 userviewer.php -> modify Tachyon BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 1 May 18th, 2004 12:38 PM
Format rounding figure : 1235.00 => 1200.00 Jane SQL Language 1 June 27th, 2003 02:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.