String Functions in SQL Server part-IV || REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM functions in SQL

Показать описание

This video will provide you the details of each string functions in SQL Server 2014. I'll be walking through each and every kinds of build in function which are available in SQL Server in my next videos.

Previous video for more String Functions

String Function Part I

String Function Part II

String Function Part III

*****************************************************************************************

REPLACE (Transact-SQL)

Replaces all occurrences of a specified string value with another string value.

 Syntax

REPLACE ( string_expression , string_pattern , string_replacement )
 Arguments
string_expression
Is the string expression to be searched. string_expression can be of a character or binary data type.
string_pattern
Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (''), and must not exceed the maximum number of bytes that fits on a page.
string_replacement
Is the replacement string. string_replacement can be of a character or binary data type.
 Return Types
Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.
Returns NULL if any one of the arguments is NULL.
If string_expression is not of type varchar(max) or nvarchar(max),REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

*****************************************************************************************

REPLICATE (Transact-SQL)

Repeats a string value a specified number of times.
 Syntax

REPLICATE ( string_expression ,integer_expression )
 Arguments
string_expression
Is an expression of a character string or binary data type. string_expression can be either character or binary data.
Note : If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.
integer_expression
Is an expression of any integer type, including bigint. If integer_expression is negative, NULL is returned.
 Return Types
Returns the same type as string_expression.

*****************************************************************************************

REVERSE (Transact-SQL)

Returns the reverse order of a string value.

 Syntax

REVERSE ( string_expression )

 Arguments
string_expression
string_expression is an expression of a string or binary data type. string_expression can be a constant, variable, or column of either character or binary data.

 Return Types
varchar or nvarchar

*****************************************************************************************

RIGHT (Transact-SQL)

Returns the right part of a character string with the specified number of characters.

 Syntax

RIGHT ( character_expression , integer_expression )

 Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convertcharacter_expression.
integer_expression
Is a positive integer that specifies how many characters of character_expression will be returned. If integer_expression is negative, an error is returned. Ifinteger_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).

 Return Types
Returns varchar when character_expression is a non-Unicode character data type.
Returns nvarchar when character_expression is a Unicode character data type.

*****************************************************************************************

RTRIM (Transact-SQL)

Returns a character string after truncating all trailing blanks.

 Syntax

RTRIM ( character_expression )

 Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

 Return Types
varchar or nvarchar