String functions

You'll be presented to the SQL functions that can perform string manipulations.


ASCII

Returns the ASCII code value of the left-most character of the character expression.

Requires:

  • character_expression: The character expression.
Syntax:  ASCII(character_expression)
SELECT ASCII('0');
--  Result: 48

CHAR

Returns the conversion of the integer ASCII code to the corresponding character.

Requires:

  • integer_expression: The integer from 0 through 255.
Syntax:  CHAR(integer_expression)
SELECT CHAR(48);
-- Result: '0'

CHARINDEX

Returns the starting position of the specified expression in the character string.

Requires:

  • expressionToFind: The character expression to find.
  • expressionToSearch: The character expression, typically a column, to search.
  • start_location (optional): The character position to start searching for expressionToFind in expressionToSearch.
Syntax:  CHARINDEX(expressionToFind ,expressionToSearch [,start_location ])
SELECT CHARINDEX('456', '0123456');
-- Result: 4

SELECT CHARINDEX('456', '0123456', 5);
-- Result: -1

CONCAT

Returns the string that is the concatenation of two or more string values.

Requires:

  • string_value1: The first string to be concatenated.
  • string_value2: The second string to be concatenated.
  • string_valueN (optional): The N string to be concatenated.
Syntax:  CONCAT(string_value1, string_value2 [, string_valueN])
SELECT CONCAT('Hello, ', 'world!');
-- Result: 'Hello, world!'

CONTAINS

Returns 1 if expressionToFind is found within expressionToSearch; otherwise, returns 0.

Requires:

  • expressionToSearch: The character expression, typically a column, to search.
  • expressionToFind: The character expression to find.
Syntax: CONTAINS(expressionToSearch, expressionToFind)
SELECT CONTAINS('0123456', '456');
-- Result: 1

SELECT CONTAINS('0123456', 'Not a number');
-- Result: 0

ENDSWITH

Returns 1 if character_expression ends with character_suffix; otherwise, returns 0.

Requires:

  • character_expression: The character expression.
  • character_suffix: The character suffix to search for.
Syntax: ENDSWITH(character_expression, character_suffix)
SELECT ENDSWITH('0123456', '456');
-- Result: 1

SELECT ENDSWITH('0123456', '012');
-- Result: 0

FORMAT

Returns the value formatted with the specified format.

Requires:

  • value: The string to format.
  • parseFormat (optional): The string specifying the input syntax of the date value. Not applicable to numeric types.
  • format: The string specifying the output syntax of the date or numeric format.
Syntax: FORMAT(value [, parseFormat], format )
SELECT FORMAT(12.34, '#');
-- Result: 12

SELECT FORMAT(12.34, '#.###');
-- Result: 12.34

SELECT FORMAT(1234, '0.000E0');
-- Result: 1.234E3

SELECT FORMAT('2019/01/01', 'yyyy-MM-dd');
-- Result: 2019-01-01

SELECT FORMAT('20190101', 'yyyyMMdd', 'yyyy-MM-dd');
-- Result: '2019-01-01'

FROM_UNIXTIME

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.

Requires:

  • time: The time stamp value from epoch time. Milliseconds are accepted, but not shown.
  • issecond: Indicates the time stamp value is milliseconds to epoch time.
Syntax: FROM_UNIXTIME(time, issecond)
SELECT FROM_UNIXTIME(1540495231, 1);
-- Result: 2018-10-25 19:20:31

SELECT FROM_UNIXTIME(1540495357385, 0);
-- Result: 2018-10-25 19:22:37

INDEXOF

Returns the starting position of the specified expression in the character string.

Requires:

  • expressionToSearch: The character expression, typically a column, to search.
  • expressionToFind: The character expression to find.
  • start_location (optional): The character position to start searching for expressionToFind in expressionToSearch.
Syntax: INDEXOF(expressionToSearch, expressionToFind [,start_location ])
SELECT INDEXOF('0123456', '456');
-- Result: 4

SELECT INDEXOF('0123456', '456', 5);
-- Result: -1

ISNULL

Replaces null with the specified replacement value.

Requires:

  • check_expression: The expression to be checked for null.
  • replacement_value: The expression to be returned if check_expression is null.
Syntax: ISNULL (check_expression, replacement_value)
SELECT ISNULL(42, 'Was NULL');
-- Result: 42

SELECT ISNULL(NULL, 'Was NULL');
-- Result: 'Was NULL'

JSON_AVG

Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return numeric value or null.

Requires:

  • json: The JSON document to compute.
  • jsonpath: The JSONPath used to select the nodes. [x], [1..], [..8], or [1..8] are accepted. [x] selects all nodes.
Syntax: JSON_AVG(json, jsonpath)
SELECT JSON_AVG('[1,2,3,4,5]', '[x]');
-- Result: 3

SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 3

SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 4.5

JSON_COUNT

Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Returns numeric value or null.

Requires:

  • json: The JSON document to compute.
  • jsonpath: The JSONPath used to select the nodes. [x], [1..], [..8], or [1..8] are accepted. [x] selects all nodes.
Syntax: JSON_COUNT(json, jsonpath)
SELECT JSON_COUNT('[1,2,3,4,5]', '[x]');
-- Result: 5

SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 5

SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 2

JSON_EXTRACT

Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Returns numeric value or null.

Requires:

  • json: The JSON document to extract.
  • jsonpath: The XPath used to select the nodes. The JSONPath must be a string constant. The values of the nodes selected will be returned in a token-separated list.
Syntax: JSON_EXTRACT(json, jsonpath)
SELECT JSON_EXTRACT('{"test": {"data": 1}}', 'test');
-- Result: '{"data":1}'

SELECT JSON_EXTRACT('{"test": {"data": 1}}', 'test.data');
-- Result: 1

SELECT JSON_EXTRACT('{"test": {"data": [1, 2, 3]}}', 'test.data[1]');
-- Result: 2

JSON_MAX

Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return numeric value or null.

Requires:

  • json: The JSON document to compute.
  • jsonpath: The JSONPath used to select the nodes. [x], [1..], [..8], or [1..8] are accepted. [x] selects all nodes.
Syntax: JSON_MAX(json, jsonpath)
SELECT JSON_MAX('[1,2,3,4,5]', '[x]');
-- Result: 5

SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 5

SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', 'test.data[..3]');
-- Result: 4

JSON_MIN

Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return numeric value or null.

Requires:

  • json: The JSON document to compute.
  • jsonpath: The JSONPath used to select the nodes. [x], [1..], [..8], or [1..8] are accepted. [x] selects all nodes.
Syntax: JSON_MIN(json, jsonpath)
SELECT JSON_MIN('[1,2,3,4,5]', '[x]');
-- Result: 1

SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 1

SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 4

JSON_SUM

Computes the summary value in JSON according to the JSONPath expression. Return numeric value or null.

Requires:

  • json: The JSON document to compute.
  • jsonpath: The JSONPath used to select the nodes. [x], [1..], [..8], or [1..8] are accepted. [x] selects all nodes.
Syntax: JSON_SUM(json, jsonpath)
SELECT JSON_SUM('[1,2,3,4,5]', '[x]');
-- Result: 15

SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 15

SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 9

LEFT

Returns the specified number of characters counting from the left of the specified string.

Requires:

  • character_expression: The character expression.
  • integer_expression: The positive integer that specifies how many characters will be returned counting from the left of character_expression.
Syntax: LEFT(character_expression, integer_expression)
SELECT LEFT('1234567890', 3);
-- Result: '123'

LEN

Returns the number of characters of the specified string expression.

Requires:

  • string_expression: The string expression.
Syntax: LEN(string_expression)
SELECT LEN('12345');
-- Result: 5

LOWER

Requires:

  • character_expression: The character expression.
Syntax: LOWER(character_expression)
SELECT LOWER('MIXED case');
-- Result: 'mixed case'

LTRIM

Returns the character expression with leading blanks removed.

Requires:

  • character_expression: The character expression.
Syntax: LTRIM(character_expression)
SELECT LTRIM('     trimmed');
-- Result: 'trimmed'

NCHAR

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

Requires:

  • integer_expression: The integer representing the Unicode character.
Syntax: NCHAR(integer_expression)
SELECT NCHAR(48);
-- Result: '0'

PATINDEX

Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.

Requires:

  • pattern: The character expression that contains the sequence to be found. The wild-card character % can be used only at the start or end of the expression.
  • expression: The expression, typically a column, to search for the pattern.
Syntax: PATINDEX(pattern, expression)
SELECT PATINDEX('123%', '1234567890');
-- Result: 1

SELECT PATINDEX('%890', '1234567890');
-- Result: 8

SELECT PATINDEX('%456%', '1234567890');
-- Result: 4

QUOTENAME

Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.

Requires:

  • character_string: The string of Unicode character data. The string is limited to 128 characters. Inputs greater than 128 characters return null.
  • quote_character (optional): The single character to be used as the delimiter. Can be a single quotation mark, a left or right bracket, or a double quotation mark. If quote_character is not specified, brackets are used.
Syntax: QUOTENAME(character_string [, quote_character])
SELECT QUOTENAME('table_name');
-- Result: '[table_name]'

SELECT QUOTENAME('table_name', '"');
-- Result: '"table_name"'

SELECT QUOTENAME('table_name', '[');
-- Result: '[table_name]'

REPLACE

Replaces all occurrences of a string with another string.

Requires:

  • string_expression: The string expression to be searched. Can be a character or binary data type.
  • string_pattern: The substring to be found. Cannot be an empty string.
  • string_replacement: The replacement string.
Syntax: REPLACE(string_expression, string_pattern, string_replacement)
SELECT REPLACE('1234567890', '456', '|');
-- Result: '123|7890'

SELECT REPLACE('123123123', '123', '.');
-- Result: '...'

SELECT REPLACE('1234567890', 'a', 'b');
-- Result: '1234567890'

REPLICATE

Repeats the string value the specified number of times.

Requires:

  • string_expression: The string to replicate.
  • integer_expression: The repeat count.
Syntax: REPLICATE(string_expression, integer_expression)
SELECT REPLACE('x', 5);
-- Result: 'xxxxx'

REVERSE

Returns the reverse order of the string expression.

Requires:

  • string_expression: The string.
Syntax: REVERSE(string_expression)
SELECT REVERSE('1234567890');
-- Result: '0987654321'

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

Requires:

  • character_expression: The character expression.
  • integer_expression: The positive integer that specifies how many characters of the character expression will be returned.
Syntax: RIGHT(character_expression, integer_expression)
SELECT RIGHT('1234567890', 3);
-- Result: '890'

RTRIM

Returns the character expression after it removes trailing blanks.

Requires:

  • character_expression: The character expression.
Syntax: RTRIM(character_expression)
SELECT RTRIM('trimmed     ');
-- Result: 'trimmed'

SOUNDEX

Returns the four-character Soundex code, based on how the string sounds when spoken.

  • character_expression: The alphanumeric expression of character data.
Syntax: SOUNDEX(character_expression)
SELECT SOUNDEX('smith');
-- Result: 'S530

SPACE

Returns the string that consists of repeated spaces.

Requires:

  • repeatcount: The number of spaces.
Syntax: SPACE(repeatcount)
SELECT SPACE(5);
-- Result: '     '

STARTSWITH

Returns 1 if character_expression starts with character_prefix; otherwise returns 0.

Requires:

  • character_expression: The character expression.
  • character_prefix: The character prefix to search for.
Syntax: STARTSWITH(character_expression, character_prefix)
SELECT STARTSWITH('0123456', '012');
-- Result: 1

SELECT STARTSWITH('0123456', '456');
-- Result: 0

STR

Returns the character data converted from the numeric data.

Requires:

  • float_expression: The float expression.
  • length (optional): The total lenght to return. This includes decimal point, sign, digits, and spaces. The default is 10.
  • decimal (optional): The number of places to the right of the decimal point. The decimal must be less than or equal to 16.
Syntax: STR(float_expression [ , integer_length [ , integer_decimal ] ] )
SELECT STR('123.456');
-- Result: '123'

SELECT STR('123.456', 2);
-- Result: '**'

SELECT STR('123.456', 10, 2);
-- Result: '123.46'

STUFF

Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Requires:

  • character_expression: The string expression.
  • integer_start: The integer value that specifies the location to start deletion and insertion. If integer_start is negative or longer than the string to be modified, null is returned.
  • integer_length: The integer that specifies the number of characters to delete. If integer_length is longer than character_expression, deletion occurs up to the last character in replaceWith_expression. If integer_length is negative, null is returned.
  • replaceWith_expressionThe expression of character data that will replace length characters of character_expression beginning at the start value.
Syntax: STUFF(character_expression, integer_start, integer_length, replaceWith_expression)
SELECT STUFF('1234567890', 2, 2, 'xx');
-- Result: '12xx567890'

SUBSTRING

Returns the part of the string with the specified length; starts at the specified index.

Requires:

  • expression: The character string.
  • start: The positive integer that specifies the start index of characters to return.
  • length: The positive integer that specifies how many characters will be returned.
Syntax: SUBSTRING(expression, integer_start, integer_length)
SELECT SUBSTRING('1234567890', 3, 2);
-- Result: '34'

TOSTRING

Converts the value of this instance to its equivalent string representation.

Requires:

  • value: The value to be converted to string.
Syntax: TOSTRING(value)
SELECT TOSTRING(123);
-- Result: '123'

SELECT TOSTRING(123.456);
-- Result: '123.456'

SELECT TOSTRING(null);
-- Result: ''

TRIM

Returns the character expression with leading and trailing blanks removed.

Requires:

  • character_expression: The character expression.
Syntax: TRIM(character_expression)
SELECT TRIM('     trimmed     ');
-- Result: 'trimmed'

UNICODE

Returns the integer value defined by the Unicode standard of the first character of the input expression.

Requires:

  • ncharacter_expression: The character expression.
Syntax: UNICODE(ncharacter_expression)
SELECT UNICODE('Hello');
-- Result: 72

UPPER

Returns the character expression with lowercase character data converted to uppercase.

Requires:

  • character_expression: The character expression.
Syntax: UPPER (character_expression)
SELECT UPPER('MIXED case');
-- Result: 'MIXED CASE'

XML_EXTRACT

Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.

Requires:

  • xml: The XML document to extract.
  • xpath: The XPath used to select the nodes. The nodes selected will be returned in a token-separated list.
  • separator (optional): The token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.
Syntax: XML_EXTRACT(xml, xpath [, separator])
SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch');
-- Result: 'a,e,i,o,u'

SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch', ';');
-- Result: 'a;e;i;o;u'