SQL Server/T-SQL Tutorial/String Functions/STR

Материал из SQL эксперт
Перейти к: навигация, поиск

SELECT STR(

   <source lang="sql">

1> SELECT STR(1, 12, 4) 2> GO


     1.0000

(1 rows affected)</source>


SELECT STR(123. 4)

   <source lang="sql">

1> SELECT STR(123.456789, 8, 4) 2> GO


123.4568 (1 rows affected)</source>


SELECT STR(

   <source lang="sql">

1> SELECT STR(1, 6, 4) 2> GO


1.0000 (1 rows affected)</source>


STR() converts a numeric value to a string

   <source lang="sql">

STR() accepts three arguments: the numeric value, the overall length, and the number of decimal positions. If the integer part of the number and decimal positions is shorter than the overall length, the result is left-padded with spaces. 9> 10> SELECT STR(123.4, 8, 4) 11> GO


123.4000 (1 rows affected)</source>


STR(float_expression, character_length, number_of_decimal_places)

   <source lang="sql">

5> CREATE TABLE discounts( 6> discounttype varchar(40) NOT NULL, 7> stor_id char(4) NULL , 8> lowqty smallint NULL, 9> highqty smallint NULL, 10> discount dec(4,2) NOT NULL 11> ) 12> GO 1> 2> insert discounts values("Initial Customer", NULL, NULL, NULL, 10.5) 3> insert discounts values("Volume Discount", NULL, 100, 1000, 6.7) 4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT discounttype, "Discount"=STR(discount, 7, 3) FROM discounts 4> GO discounttype Discount


--------

Initial Customer 10.500 Volume Discount 6.700 Customer Discount 5.000 (3 rows affected) 1> 2> drop table discounts; 3> GO 1></source>


STR takes a numeric value and changes the data type to a char.

   <source lang="sql">

STR(number_to_convert, length_of_string, [number_of_decimal_places]) 8> SELECT STR(100.325,7,2) 9> GO


100.33

(1 rows affected) 1> 2> SELECT STR(100.325,7) 3> GO


   100

(1 rows affected)</source>