Transact-SQL string and datetime functions

String functions in MS SQL SERVER

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

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,

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 "*".

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

Console

Execute

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

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

    Scheme

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

Console

Execute

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

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

    Scheme

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:

's name>_s id>

Console

Execute

SELECT name + '_' + CAST(id_psg AS VARCHAR) 
FROM Passenger

SELECT name + '_' + CAST(id_psg AS VARCHAR) FROM Passenger

    Scheme

No scheme

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:

Console

Execute

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

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

    Scheme

No scheme