Tutorial by Examples

Returns a sub string starting with the left most char of a string and up to the maximum length specified. Parameters: character expression. The character expression can be of any data type that can be implicitly converted to varchar or nvarchar, except for text or ntext max length. An integer n...

Returns a sub string that is the right most part of the string, with the specified max length. Parameters: character expression. The character expression can be of any data type that can be implicitly converted to varchar or nvarchar, except for text or ntext max length. An integer number betwe...

Returns a substring that starts with the char that's in the specified start index and the specified max length. Parameters: Character expression. The character expression can be of any data type that can be implicitly converted to varchar or nvarchar, except for text or ntext. Start index. A nu...

Returns an int value representing the ASCII code of the leftmost character of a string. SELECT ASCII('t') -- Returns 116 SELECT ASCII('T') -- Returns 84 SELECT ASCII('This') -- Returns 84 If the string is Unicode and the leftmost character is not ASCII but representable in the current collatio...

Returns the start index of a the first occurrence of string expression inside another string expression. Parameters list: String to find (up to 8000 chars) String to search (any valid character data type and length, including binary) (Optional) index to start. A number of type int or big int. ...

Returns a char represented by an int ASCII code. SELECT CHAR(116) -- Returns 't' SELECT CHAR(84) -- Returns 'T' This can be used to introduce new line/line feed CHAR(10), carriage returns CHAR(13), etc. See AsciiTable.com for reference. If the argument value is not between 0 and 255, the CHAR...

Returns the number of characters of a string. Note: the LEN function ignores trailing spaces: SELECT LEN('My string'), -- returns 9 LEN('My string '), -- returns 9 LEN(' My string') -- returns 12 If the length including trailing spaces is desired there are several techniques...

SQL Server 2012 Returns a string that is the result of two or more strings joined together. CONCAT accepts two or more arguments. SELECT CONCAT('This', ' is', ' my', ' string') -- returns 'This is my string' Note: Unlike concatenating strings using the string concatenation operator (+), when ...

Returns a character expression (varchar or nvarchar) after converting all uppercase characters to lowercase. Parameters: Character expression. Any expression of character or binary data that can be implicitly converted to varchar. SELECT LOWER('This IS my STRING') -- Returns 'this is my strin...

Returns a character expression (varchar or nvarchar) after converting all lowercase characters to uppercase. Parameters: Character expression. Any expression of character or binary data that can be implicitly converted to varchar. SELECT UPPER('This IS my STRING') -- Returns 'THIS IS MY STRIN...

Returns a character expression (varchar or nvarchar) after removing all leading white spaces, i.e., white spaces from the left through to the first non-white space character. Parameters: character expression. Any expression of character or binary data that can be implicitly converted to varcher,...

Returns a character expression (varchar or nvarchar) after removing all trailing white spaces, i.e., spaces from the right end of the string up until the first non-white space character to the left. Parameters: character expression. Any expression of character or binary data that can be implicit...

Returns the integer value representing the Unicode value of the first character of the input expression. Parameters: Unicode character expression. Any valid nchar or nvarchar expression. SELECT UNICODE(N'Ɛ') -- Returns 400 DECLARE @Unicode nvarchar(11) = N'Ɛ is a char' SELECT UNICODE(@Uni...

Returns the Unicode character(s) (nchar(1) or nvarchar(2)) corresponding to the integer argument it receives, as defined by the Unicode standard. Parameters: integer expression. Any integer expression that is a positive number between 0 and 65535, or if the collation of the database supports sup...

Returns a string value in reversed order. Parameters: string expression. Any string or binary data that can be implicitly converted to varchar. Select REVERSE('Sql Server') -- Returns 'revreS lqS'

Returns the starting position of the first occurrence of a the specified pattern in the specified expression. Parameters: pattern. A character expression the contains the sequence to be found. Limited to A maximum length of 8000 chars. Wildcards (%, _) can be used in the pattern. If the patter...

Returns a string (varchar) of repeated spaces. Parameters: integer expression. Any integer expression, up to 8000. If negative, null is returned. if 0, an empty string is returned. (To return a string longer then 8000 spaces, use Replicate. SELECT SPACE(-1) -- Returns NULL SELECT SPACE(0) -...

Repeats a string value a specified number of times. Parameters: string expression. String expression can be a character string or binary data. integer expression. Any integer type, including bigint. If negative, null is returned. If 0, an empty string is returned. SELECT REPLICATE('a', -1) ...

Returns a string (varchar or nvarchar) where all occurrences of a specified sub string is replaced with another sub string. Parameters: string expression. This is the string that would be searched. It can be a character or binary data type. pattern. This is the sub string that would be replaced...

SQL Server 2016 Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued function and therefore must be used within FROM clause. Parameters: string. Any character type expression (char, nchar, varchar or nvarchar) seperator. A single character expressi...