Professional Web Applications Themes

string formatting - Microsoft SQL / MS SQL Server

I need to convert a numeric filed NUMERIC(14,3) to a string with a (european) regional currency formatting like "1.234,56". How can I achieve this, please ?...

  1. #1

    Default string formatting

    I need to convert a numeric filed NUMERIC(14,3) to a
    string with a (european) regional currency formatting
    like "1.234,56". How can I achieve this, please ?
    Michael Guest

  2. #2

    Default Re: string formatting

    I suggest you do this in the client application (where formatting really belongs). There is no
    direct style for this format in the CONVERT function (see the 3:rd parameter). The closest is
    the reverse (regarding comma and point):

    DECLARE s NUMERIC(14,3)
    SET s = 12332312.456
    SELECT CONVERT(varchar(20), CAST(s AS money), 1)

    What you can do with above expression is to take the stuff apart from the last 3 characters and
    replace comma with dot. And then replace the 3:rd character from the right with a comma. Not the
    easiest expression to read, though. Shown in steps below:

    DECLARE s NUMERIC(14,3)
    SET s = 12332312.456
    SELECT CONVERT(varchar(20), CAST(s AS money), 1)

    SELECT REPLACE(CONVERT(varchar(20), CAST(s AS money), 1), ',', '.')
    SELECT STUFF(REVERSE(REPLACE(CONVERT(varchar(20), CAST(s AS money), 1), ',', '.')), 3, 1, ',')
    SELECT REVERSE(STUFF(REVERSE(REPLACE(CONVERT(varchar(20), CAST(s AS money), 1), ',', '.')), 3,
    1, ','))



    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "Michael" <m.giessiaspa.com> wrote in message news:088201c345ef$111dc5d0$a501280aphx.gbl...
    > I need to convert a numeric filed NUMERIC(14,3) to a
    > string with a (european) regional currency formatting
    > like "1.234,56". How can I achieve this, please ?

    Tibor Karaszi Guest

  3. #3

    Default Re: string formatting

    Thanks for your fast reply. I will try to do the formatting in Word
    with the /# numeric switch before trying your function.
    Thanks a lot again.

    "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszicorner stone.se> wrote in message news:<OEflCHfRDHA.2084TK2MSFTNGP11.phx.gbl>...
    > I suggest you do this in the client application (where formatting really belongs). There is no
    > direct style for this format in the CONVERT function (see the 3:rd parameter). The closest is
    > the reverse (regarding comma and point):
    >
    > DECLARE s NUMERIC(14,3)
    > SET s = 12332312.456
    > SELECT CONVERT(varchar(20), CAST(s AS money), 1)
    >
    > What you can do with above expression is to take the stuff apart from the last 3 characters and
    > replace comma with dot. And then replace the 3:rd character from the right with a comma. Not the
    > easiest expression to read, though. Shown in steps below:
    >
    > DECLARE s NUMERIC(14,3)
    > SET s = 12332312.456
    > SELECT CONVERT(varchar(20), CAST(s AS money), 1)
    >
    > SELECT REPLACE(CONVERT(varchar(20), CAST(s AS money), 1), ',', '.')
    > SELECT STUFF(REVERSE(REPLACE(CONVERT(varchar(20), CAST(s AS money), 1), ',', '.')), 3, 1, ',')
    > SELECT REVERSE(STUFF(REVERSE(REPLACE(CONVERT(varchar(20), CAST(s AS money), 1), ',', '.')), 3,
    > 1, ','))
    >
    >
    >
    > --
    > Tibor Karaszi, SQL Server MVP
    > Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]
    >
    >
    > "Michael" <m.giessiaspa.com> wrote in message news:088201c345ef$111dc5d0$a501280aphx.gbl...
    > > I need to convert a numeric filed NUMERIC(14,3) to a
    > > string with a (european) regional currency formatting
    > > like "1.234,56". How can I achieve this, please ?
    Michael Gies Guest

Similar Threads

  1. Formatting String in Datagrid
    By xianxian in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: September 16th, 01:12 PM
  2. Formatting Number/String Help Needed...
    By e-roq777 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 1st, 11:19 AM
  3. Long string ruining formatting
    By Cathie Hagen in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: August 28th, 02:25 AM
  4. Formatting a string for entry into MySQL
    By James Johnson in forum PHP Development
    Replies: 2
    Last Post: September 23rd, 03:19 PM
  5. STRING FORMATTING QUESTION
    By Dale Hersh in forum PHP Development
    Replies: 1
    Last Post: July 17th, 06:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139