TEXT

Updated 2023-11-10 16:08:55.653000

Syntax

SELECT [westclintech].[wct].[TEXT] (
  <@Value, float,>
 ,<@Format, nvarchar(4000),>)

Description

Use the scalar function TEXT to convert a numeric value to text and specify the display formatting by using special format strings.

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 Type

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

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 positive, negative, and zero values.

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;