Login     Register

        Contact Us     Search

XLeratorDB/strings Online Documentation

SQL Server TEXT function


TEXT

Updated: 30 April 2009


Use wct.TEXT to convert a numeric value to text and specify the display formatting by using special format strings.
Syntax
SELECT [wctString].[wct].[TEXT] (
   <@Value, float,>
 ,<@Format, nvarchar(4000),>)
Arguments
@Value
is the value to be formatted. @Value is an expression of type float or of a type that can be implicitly converted to float
 
@Format
A numeric format as a text string enclosed in quotation marks, for example 'm/d/yyyy' or '#,###.00'. The @Format argument can be of data types that are implicitly convertible to nvarchar or ntext.
Return Types
nvarchar(4000)
Remarks
·         TEXT treats date formats by interpreting the number as the number of days since December 31, 1899, consistent with EXCEL. This is different than how SQL Server stores date in a datetime field.
Number formats
·         Use zero (0) in @Format to display insignificant zeroes if @Value has fewer digits than there are in the format. For example, if you always wanted to display 5 decimal places to the right of the decimal, then @Format would end in '.00000'.
·         Use the symbol (#) in @Format to suppress leading and trailing zeroes. If you wanted to display up to 5 decimal places, but no more, then @Format would end in '.#####'.
·         Use the symbol (?) in @Format to display spaces in place of insignificant zeroes if @Value has fewer digits than there are in @Format. This could be used to make sure that the decimal places are lined up in the resultant table.
·         Use the period (.) in @Format to display the decimal point in a number.
·         Use the comma (,) in @Format as the thousands separator.
·         A comma (,) at the end of @Format scales @Value by one thousand. You can use multiple commas; each one scaling by one thousand, so 2 commas would scale by one million, 3 commas by one billion, etc.
·         Use the symbol (/) in @Format to identify that you want the result returned as a fraction. The (/) symbol can be used in conjunction with the other symbols. You cannot have more than 3 digits in the denominator. If you need fractions with more than 3 digits, use the DEC2FRAC function.
·         Put the dollar grammalogue ($) at the beginning of @Format to display a currency value. You can specify other currencies by using the appropriate symbols: (¢), (£), (¥), and ().
·         Use the percent sign (%) in @Format to display @Value as a percent.
·         Use the letter E (E, e, E+, E-, e+, e-) in @Format to display @Value in scientific notation. For example, if @Format = '0.00E+00' and @Value = 299792458, then the result would be 3.00E+08. If @Format = '0.###E+00', then the result would be 2.998E+08.
·         Use the semi-colon (;) to identify different formats for positive and negative values or postive, negative, and zero values.
Date and time formats
·         Use m to display the month as a number without a leading zero.
·         Use mm to display the month as a number with a leading zero when appropriate.
·         Use mmm to display the month as an abbreviation (Jan to Dec).
·         Use mmmm to display the month as a full name (January to December).
·         Use mmmmm to display the month as a single letter (J to D).
·         Use d to displays the day as a number without a leading zero.
·         Use dd to display the day as a number with a leading zero when appropriate.
·         Use ddd to display the day as an abbreviation (Sun to Sat).
·         Use dddd to display the day as a full name (Sunday to Saturday).
·         Use yy to display the year as a two-digit number.
·         Use yyyy to display the year as a four-digit number.
·         Use h to display the hour as a number without a leading zero.
·         Use [h] to display elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss.
·         Use hh to display the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is shown based on the 12-hour clock. Otherwise, the hour is shown based on the 24-hour clock.
·         Use m to Display the minute as a number without a leading zero.  The m or the mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, the month is displayed instead of minutes.
·         Use [m] to display elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.
·         Use mm to display the minute as a number with a leading zero when appropriate. The m or the mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, the month is displayed instead of minutes.
·         Use s to display the second as a number without a leading zero.
·         Use [s] to display elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss].
·         Use ss to display the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00.
·         Use AM/PM, am/pm, A/P, or a/p to displays the hour based on a 12-hour clock.
Examples
These are some basic examples dealing with decimal places and significant digits. We have used a temporary table to simplify the examples.
CREATE TABLE #t (
      [recno] [float] NOT NULL,
      [num_val] [float] NOT NULL,
      [num_format] [varchar](50) NOT NULL
)
INSERT INTO #t VALUES( 1, 1234567890, '#,#')
INSERT INTO #t VALUES( 2, 1234567890, '###')
INSERT INTO #t VALUES( 3, 1234567890, '###.##')
INSERT INTO #t VALUES( 4, 1234567890, '###.00')
INSERT INTO #t VALUES( 5, 22.98643, '###.000')
INSERT INTO #t VALUES( 6, 22.98643, '0.#')
INSERT INTO #t VALUES( 7, 22.98643, '#.0#')
INSERT INTO #t VALUES( 8, 44.398, '???.???')
INSERT INTO #t VALUES( 9, 102.65, '???.???')
INSERT INTO #t VALUES( 10, 2.8, '???.???')
INSERT INTO #t VALUES( 11, 5.25, '# ???/???')
INSERT INTO #t VALUES( 12, 5.3, '# ???/???')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno       num_val                num_format     
----------- ---------------------- --------------- ----------------------------
1           1234567890             #,#             1,234,567,890
2           1234567890             ###             1234567890
3           1234567890             ###.##          1234567890.
4           1234567890             ###.00          1234567890.00
5           22.98643               ###.000         22.986
6           22.98643               0.#             23.
7           22.98643               #.0#            22.99
8           44.398                 ???.???          44.398
9           102.65                 ???.???         102.65
10          2.8                    ???.???           2.8 
11          5.25                   # ???/???       5   1/4 
12          5.3                    # ???/???       5   3/10


Scientific notation
CREATE TABLE #t (
      [recno] [int] NOT NULL,
      [num_val] [float] NOT NULL,
      [num_format] [varchar](15) NOT NULL
INSERT INTO #t VALUES( 13, 1234567890, '0.00E00')
INSERT INTO #t VALUES( 14, 1234567890, '#.##E00')
INSERT INTO #t VALUES( 15, 1234567890, '0.00E+00')
INSERT INTO #t VALUES( 16, 1234567890, '#.##E+00')
INSERT INTO #t VALUES( 17, 1234567890, '0.00E-00')
INSERT INTO #t VALUES( 18, 1234567890, '#.##E-00')
INSERT INTO #t VALUES( 19, .00001234567890, '0.000E00')
INSERT INTO #t VALUES( 20, .00001234567890, '#.###E00')
INSERT INTO #t VALUES( 21, .00001234567890, '0.00E+00')
INSERT INTO #t VALUES( 22, .00001234567890, '#.##E+00')
INSERT INTO #t VALUES( 23, .00001234567890, '0.00E-0')
INSERT INTO #t VALUES( 24, .00001234567890, '#.##E-0')
select *, wct.TEXT(num_val, num_format)
from #t
 
This produces the following result
recno       num_val                num_format     
----------- ---------------------- --------------- ---------------------------
13          1234567890             0.00E00         1.23E09
14          1234567890             #.##E00         1.23E09
15          1234567890             0.00E+00        1.23E+09
16          1234567890             #.##E+00        1.23E+09
17          1234567890             0.00E-00        1.23E09
18          1234567890             #.##E-00        1.23E09
19          1.23456789E-05         0.000E00        1.235E-05
20          1.23456789E-05         #.###E00        1.235E-05
21          1.23456789E-05         0.00E+00        1.23E-05
22          1.23456789E-05         #.##E+00        1.23E-05
23          1.23456789E-05         0.00E-0         1.23E-5
24          1.23456789E-05         #.##E-0         1.23E-5
 
(12 row(s) affected)
Thousands separator
CREATE TABLE #t (
      [recno] [int] NOT NULL,
      [num_val] [float] NOT NULL,
      [num_format] [varchar](15) NOT NULL
)
INSERT INTO #t VALUES( 25, 1234567890, '#,###')
INSERT INTO #t VALUES( 26, 1234567890, '#,###,')
INSERT INTO #t VALUES( 27, 1234567890, '#,###,,')
select *, wct.TEXT(num_val, num_format)
from #t
 
This produces the following result
recno       num_val                num_format     
----------- ---------------------- --------------- ----------------------------
25          1234567890             #,###           1,234,567,890
26          1234567890             #,###,          1,234,568
27          1234567890             #,###,,         1,235
 
(3 row(s) affected)


Dates
CREATE TABLE #t (
      [recno] [int] NOT NULL,
      [num_val] [float] NOT NULL,
      [num_format] [varchar](20) NOT NULL
)
INSERT INTO #t VALUES( 28, 39933, 'mm/dd/yy')
INSERT INTO #t VALUES( 29, 39933, 'dd/mm/yy')
INSERT INTO #t VALUES( 30, 39933, 'd/m/yy')
INSERT INTO #t VALUES( 31, 39933, 'm/d/yy')
INSERT INTO #t VALUES( 32, 39933, 'mm/dd/yyyy')
INSERT INTO #t VALUES( 33, 39933, 'dd/mm/yyyy')
INSERT INTO #t VALUES( 34, 39933, 'd/m/yyyy')
INSERT INTO #t VALUES( 35, 39933, 'm/d/yyyy')
INSERT INTO #t VALUES( 36, 39933, 'yymmdd')
INSERT INTO #t VALUES( 37, 39933, 'dddd, dd mmmm yyyy')
INSERT INTO #t VALUES( 38, 39933, 'ddd, dd mmm yyyy')
INSERT INTO #t VALUES( 39, 39933, 'mmmmm-yyyy')
select *, wct.TEXT(num_val, num_format)
from #t
 
This produces the following result
recno       num_val                num_format          
----------- ---------------------- -------------------- ----------------------
28          39933                  mm/dd/yy             04/30/09
29          39933                  dd/mm/yy             30/04/09
30          39933                  d/m/yy               30/4/09
31          39933                  m/d/yy               4/30/09
32          39933                  mm/dd/yyyy           04/30/2009
33          39933                  dd/mm/yyyy           30/04/2009
34          39933                  d/m/yyyy             30/4/2009
35          39933                  m/d/yyyy             4/30/2009
36          39933                  yymmdd               090430
37          39933                  dddd, dd mmmm yyyy   Thursday, 30 April 2009
38          39933                  ddd, dd mmm yyyy     Thu, 30 Apr 2009
39          39933                  mmmmm-yyyy           A-2009
 
(12 row(s) affected)
 
Times
CREATE TABLE #t (
      [recno] [int] NOT NULL,
      [num_val] [float] NOT NULL,
      [num_format] [varchar](20) NOT NULL
)
INSERT INTO #t VALUES( 40, 0.5892361, 'h:m:s')
INSERT INTO #t VALUES( 41, 0.5892361, 'hh:mm:ss')
INSERT INTO #t VALUES( 42, 0.5892361, 'h:mm:ss AM/PM')
INSERT INTO #t VALUES( 43, 0.5892361, 'h:mm AM/PM')
INSERT INTO #t VALUES( 44, 0.5892361, '[hh]:mm:ss')
INSERT INTO #t VALUES( 45, 0.5892361, '[mm]:ss')
INSERT INTO #t VALUES( 46, 0.5892361, '[ss]')
select *, wct.TEXT(num_val, num_format)
from #t
 
This produces the following result
recno       num_val                num_format          
----------- ---------------------- -------------------- ----------------------
40          0.5892361              h:m:s                14:8:30
41          0.5892361              hh:mm:ss             14:08:30
42          0.5892361              h:mm:ss AM/PM        2:08:30 PM
43          0.5892361              h:mm AM/PM           2:08 PM
44          0.5892361              [hh]:mm:ss           14:08:30
45          0.5892361              [mm]:ss              848:30
46          0.5892361              [ss]                 50910
 
(7 row(s) affected)


Date & Time
CREATE TABLE #t (
      [recno] [int] NOT NULL,
      [num_val] [float] NOT NULL,
      [num_format] [varchar](25) NOT NULL
)
INSERT INTO #t VALUES( 47, 39933.5892361, 'm/d/yy h:m:s')
INSERT INTO #t VALUES( 48, 39933.5892361, 'mm/dd/yyyy hh:mm:ss')
INSERT INTO #t VALUES( 49, 39933.5892361, 'mm/dd/yyyy h:mm AM/PM')
INSERT INTO #t VALUES( 50, 39933.5892361, 'dddd, mm/dd/yy h:mm AM/PM')
select *, wct.TEXT(num_val, num_format)
from #t
 
This produces the following result
recno       num_val                num_format               
----------- ---------------------- ------------------------- -------------------
47          39933.5892361          m/d/yy h:m:s              04/30/09 14:8:30
48          39933.5892361          mm/dd/yyyy hh:mm:ss       04/30/2009 14:08:30
49          39933.5892361          mm/dd/yyyy h:mm AM/PM     04/30/2009 2:08 PM
50          39933.5892361          dddd, mm/dd/yy h:mm AM/PM Thursday, 04/30/09 2:08 PM
 
(4 row(s) affected)
 
Percents, currencies, and semi-colons
CREATE TABLE #t (
      [recno] [int] NOT NULL,
      [num_val] [float] NOT NULL,
      [num_format] [varchar](25) NOT NULL
)
INSERT INTO #t VALUES( 51, 1.5, '#.00%')
INSERT INTO #t VALUES( 52, -0.75, '#.00%')
INSERT INTO #t VALUES( 53, 1000000, '$#,#.00')
INSERT INTO #t VALUES( 54, -1000000, '$#,#.00')
INSERT INTO #t VALUES( 55, 1000000, '€#,#.00')
INSERT INTO #t VALUES( 56, -1000000, '€#,#.00;(€#,#.00)')
INSERT INTO #t VALUES( 57, 1000000, '¥#,#;(¥#,#)')
INSERT INTO #t VALUES( 58, -1000000, '¥#,#;(¥#,#)')
select *, wct.TEXT(num_val, num_format)
from #t
 
This produces the following result
recno       num_val                num_format               
----------- ---------------------- ------------------------- -------------------
51          1.5                    #.00%                     150.00%
52          -0.75                  #.00%                     -75.00%
53          1000000                $#,#.00                   $1,000,000.00
54          -1000000               $#,#.00                   -$1,000,000.00
55          1000000                €#,#.00                   €1,000,000.00
56          -1000000               €#,#.00;(€#,#.00)         (€1,000,000.00)
57          1000000                ¥#,#;(¥#,#)               ¥1,000,000
58          -1000000               ¥#,#;(¥#,#)               (¥1,000,000)


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service