String Functions in SQL Server part-III || NCHAR, UNICODE, PATINDEX, QUOTENAME 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

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

NCHAR
Returns the Unicode character with the specified integer code, as defined by the Unicode standard.

Syntax

NCHAR ( integer_expression )

Arguments
integer_expression
When the collation of the database does not contain the supplementary character (SC) flag, this is a positive whole number from 0 through 65535 (0 through 0xFFFF). If a value outside this range is specified, NULL is returned. For more information about supplementary characters, see Collation and Unicode Support.

When the collation of the database supports the supplementary character (SC) flag, this is a positive whole number from 0 through 1114111 (0 through 0x10FFFF). If a value outside this range is specified, NULL is returned.

Return Types
nchar(1) when the default database collation does not support supplementary characters.
nvarchar(2) when the default database collation supports supplementary characters.
If the parameter integer_expression lies in the range 0 - 0xFFFF, only one character is returned. For higher values, NCHAR returns the corresponding surrogate pair. Do not construct a surrogate pair by using NCHAR(High surrogate) + NCHAR(Low Surrogate). Instead, use a database collation that supports supplementary characters and then specify the Unicode codepoint for the surrogate pair. The following example demonstrates both the old style method of constructing a surrogate pair and the preferred method of specifying the Unicode codepoint.

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

UNICODE
Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.

Syntax

UNICODE ( 'ncharacter_expression' )

 Arguments
' ncharacter_expression '
Is an nchar or nvarchar expression.

 Return Types
int

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

PATINDEX
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

Syntax

PATINDEX ( '%pattern%' , expression )

Arguments
pattern
Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category. pattern is limited to 8000 characters.
expression
Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.

 Return Types
bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int.

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

QUOTENAME

Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

 Syntax

QUOTENAME ( 'character_string' [ , 'quote_character' ] )

Arguments
'character_string'
Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.
'quote_character'
Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), or a double quotation mark ( " ). Ifquote_character is not specified, brackets are used.

Return Types
nvarchar(258)