Русский    English

STR, SPACE, LTRIM and RTRIM functions

STR() function converts a number to its string representation:

  1. STR ( float_expression [ , length [ , decimal ] ] )

Under this conversion the round-up is being worked, and a length specifies a length of the resulting string. For example,

STR(3.3456, 5, 1) 3.3
STR(3.3456, 5, 2) 3.35
STR(3.3456, 5, 3) 3.346
STR(3.3456, 5, 4) 3.346

Take note, that if have got string view of the number is less than the specified length, the leading spaces are added. If the result is greater than the specified length, the fractional part is being truncated (with the round-up); in case there is an integer value we get the corresponding number of the asterisks "*".

  1. STR(12345,4,0) ****

By the way, default length is 10 symbols. Keeping in mind that a year is represented by four numbers, we will write

  1. SELECT name, STUFF(name, 6, LEN(name), '_'+STR(launched, 4))
  2. FROM Ships

Everything is almost right. The case is rested to consider, when the number of symbols in the name is less than 6, as in this case the function STUFF gives NULL. Well, we will have to be on the rack to the end, which is connected with using this function in this example, on the way applying one more string function. We'll add the trailing blanks for the name`s length being knowingly more than 6. There is a special SPACE function for that

  1. SELECT name, STUFF(name + SPACE(6), 6, LEN(name), '_'+STR(launched,4))
  2. FROM Ships

The following functions - LTRIM(character_expression) and RTRIM(character_expression) - cut off accordingly the leading and trailing blanks from the string expression, which is being converted by default to the VARCHAR data type.

Let us need to build such a string:

  1. 's name>_s id>
for every record from the Passenger table. If we write

  1. SELECT name + '_' + CAST(id_psg AS VARCHAR)
  2. FROM Passenger
we will get in the result something like this

A _1

It is connected with the fact that the column name has the type CHAR(30). The short string is added with the blanks for this type to the specified dimension (we have 30 symbols). The function RTRIM will help us here:

  1. SELECT RTRIM(name) + '_' + CAST(id_psg AS VARCHAR)
  2. FROM Passenger

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100