String

​

ASCII

ASCII(character_expression)

• character_expression The character expression.

SELECT ASCII('0');
--  Result: 48

​

CHAR

CHAR(character_expression)

• integer_expression The integer from 0 through 255.

SELECT CHAR(48);
-- Result: '0'

​

CHARINDEX

CHARINDEX(expressionToFind ,expressionToSearch [,start_location ])

• expressionToFind The character expression to find.
• expressionToSearch The character expression, typically a column, to search.
• start_location The optional character position to start searching for expressionToFind in expressionToSearch.

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

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

​

CHAR_LENGTH

CHAR_LENGTH(character_expression)

• character_expression The set of characters to be evaluated for length.

SELECT CHAR_LENGTH('sample text') FROM Account LIMIT 1
-- Result: 11

​

CONCAT

CONCAT(string_value1, string_value2 [, string_valueN])

• string_value1 The first string to be concatenated.
• string_value2 The second string to be concatenated.
• string_valueN The optional additional strings to be concatenated.

SELECT CONCAT('Hello, ', 'world!');
-- Result: 'Hello, world!'

​

CONTAINS

CONTAINS(expressionToSearch, expressionToFind)

• expressionToSearch The character expression, typically a column, to search.
• expressionToFind The character expression to find.

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

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

​

ENDSWITH

ENDSWITH(character_expression, character_suffix)

• character_expression The character expression.
• character_suffix The character suffix to search for.

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

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

​

FILESIZE

FILESIZE(uri)

• uri The path of the file to read the size from.

SELECT FILESIZE('C:/Users/User1/Desktop/myfile.txt');
-- Result: 23684

​

FORMAT

FORMAT(value [, parseFormat], format )

• value The string to format.
• parseFormat 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.

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

FROM_UNIXTIME(time, issecond)

• time The time stamp value from epoch time. Milliseconds are accepted.
• issecond Indicates the time stamp value is milliseconds to epoch time.

SELECT FROM_UNIXTIME(1540495231, 1);
-- Result: 2018-10-25 19:20:31

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

​

HASHBYTES

HASHBYTES(algorithm, value)

• algorithm The algorithm to use for hashing. Must be one of MD5, SHA1, SHA2_256, or SHA2_512.
• value The value to hash. Must be either a string or byte array.

SELECT HASHBYTES('MD5', 'Test');
-- Result (byte array): 0x0CBC6611F5540BD0809A388DC95A615B

​

INDEXOF

INDEXOF(expressionToSearch, expressionToFind [,start_location ])

• expressionToSearch The character expression, typically a column, to search.
• expressionToFind The character expression to find.
• start_location The optional character position to start searching for expressionToFind in expressionToSearch.

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

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

​

ISNULL

ISNULL ( check_expression , replacement_value )

• check_expression The expression to be checked for null.
• replacement_value The expression to be returned if check_expression is null.

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

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

​

JSON_AVG

JSON_AVG(json, jsonpath)

• json The JSON document to compute.
• jsonpath The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

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

JSON_COUNT(json, jsonpath)

• json The JSON document to compute.
• jsonpath The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

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

JSON_EXTRACT(json, jsonpath)

• 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.

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

JSON_MAX(json, jsonpath)

• json The JSON document to compute.
• jsonpath The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

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

JSON_MIN(json, jsonpath)

• json The JSON document to compute.
• jsonpath The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

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

JSON_SUM(json, jsonpath)

• json The JSON document to compute.
• jsonpath The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

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

LEFT(character_expression , integer_expression)

• 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.

SELECT LEFT('1234567890', 3);
-- Result: '123'

​

LEN

LEN(string_expression)

• string_expression The string expression.

SELECT LEN('12345');
-- Result: 5

​

LOCATE

LOCATE(substring,string)

• substring The substring to find inside larger string.
• string The larger string that will be searched for the substring.

SELECT LOCATE('sample','XXXXXsampleXXXXX');
-- Result: 6

​

LOWER

LOWER(character_expression)

• character_expression The character expression.

SELECT LOWER('MIXED case');
-- Result: 'mixed case'

​

LTRIM

LTRIM(character_expression)

• character_expression The character expression.

SELECT LTRIM('   trimmed');
-- Result: 'trimmed'

​

MASK

MASK(string_expression, mask_character [, start_index [, end_index ]])

• string_expression The string expression to be searched.
• mask_character The character to mask with.
• start_index The optional number of characters to leave unmasked at beginning of string. Defaults to 0.
• end_index The optional number of characters to leave unmasked at end of string. Defaults to 0.

SELECT MASK('1234567890','*',);
-- Result: '**********'

SELECT MASK('1234567890','*', 4);
-- Result: '1234******'

SELECT MASK('1234567890','*', 4, 2);
-- Result: '1234****90'

​

NCHAR

NCHAR(integer_expression)

• integer_expression The integer from 0 through 255.

​

OCTET_LENGTH

OCTET_LENGTH(character_expression)

• character_expression The set of characters to be evaluated.

SELECT OCTET_LENGTH('text') FROM Account LIMIT 1
-- Result: 4

​

PATINDEX

PATINDEX(pattern, expression)

• 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.

SELECT PATINDEX('123%', '1234567890');
-- Result: 1

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

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

​

POSITION

POSITION(expressionToFind IN expressionToSearch)

• expressionToFind The character expression to find.
• expressionToSearch The character expression, typically a column, to search.

SELECT POSITION('456'\` \`IN '123456');
-- Result: 4

SELECT POSITION('x'\` \`IN '123456');
-- Result: 0

​

QUOTENAME

QUOTENAME(character_string [, quote_character])

• character_string The string of Unicode character data. The string is limited to 128 characters. Inputs greater than 128 characters return null.
• quote_character The optional 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.

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

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

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

​

REPLACE

REPLACE(string_expression, string_pattern, string_replacement)

• 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.

SELECT REPLACE('1234567890', '456', '|');
-- Result: '123|7890'

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

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

​

REPLICATE

REPLICATE(string_expression ,integer_expression)

• string_expression The string to replicate.
• integer_expression The repeat count.

SELECT REPLACE('x', 5);
-- Result: 'xxxxx'

​

REVERSE

REVERSE(string_expression)

• string_expression The string.

SELECT REVERSE('1234567890');
-- Result: '0987654321'

​

RIGHT

RIGHT ( character_expression , integer_expression )

• character_expression The character expression.
• integer_expression The positive integer that specifies how many characters of the character expression will be returned.

SELECT RIGHT('1234567890', 3);
-- Result: '890'

​

RTRIM

RTRIM(character_expression)

• character_expression The character expression.

SELECT RTRIM('trimmed   ');
-- Result: 'trimmed'

​

SOUNDEX

SOUNDEX(character_expression)

• character_expression The alphanumeric expression of character data.

SELECT SOUNDEX('smith');
-- Result: 'S530'

​

SPACE

SPACE(repeatcount)

• repeatcount The number of spaces.

SELECT SPACE(5);
-- Result: '   '

​

SPLIT

SPLIT(string, delimiter, offset)

• string The string to split.
• delimiter The character to split the string with.
• offset The number of the split to return. Positive numbers are treated as offsets from the left, and negative numbers are treated as offsets from the right.

SELECT SPLIT('a/b/c/d', '/', 1);
-- Result: 'a'

SELECT SPLIT('a/b/c/d', '/', -2);
-- Result: 'c'

​

STARTSWITH

STARTSWITH(character_expression, character_prefix)

• character_expression The character expression.
• character_prefix The character prefix to search for.

SELECT STARTSWITH('0123456', '012');
-- Result: 1

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

​

STR

STR(float_expression [ , integer_length [ , integer_decimal ] ])

• float_expression The float expression.
• length The optional total length to return. This includes decimal point, sign, digits, and spaces. The default is 10.
• decimal The optional number of places to the right of the decimal point. The decimal must be less than or equal to 16.

SELECT STR('123.456');
-- Result: '123'

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

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

​

STUFF

STUFF(character_expression , integer_start , integer_length , replaceWith_expression)

• character_expression The string expression.
• start The integer value that specifies the location to start deletion and insertion. If start or length is negative, null is returned. If start is longer than the string to be modified, character_expression, null is returned.
• length The integer that specifies the number of characters to delete. If length is longer than character_expression, deletion occurs up to the last character in replaceWith_expression.
• replaceWith_expression The expression of character data that will replace length characters of character_expression beginning at the start value.

SELECT STUFF('1234567890', 3, 2, 'xx');
-- Result: '12xx567890'

​

SUBSTRING

SUBSTRING(string_value FROM start FOR length)

• string_value The character string.
• start The positive integer that specifies the start index of characters to return.
• length Optional. The positive integer that specifies how many characters will be returned.

SELECT SUBSTRING('1234567890' FROM 3 FOR 2);
-- Result: '34'

SELECT SUBSTRING('1234567890' FROM 3);
-- Result: '34567890'

​

TOSTRING

TOSTRING(string_value1)

• string_value1 The string to be converted.

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

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

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

​

TRIM

TRIM(trimspec trimchar FROM string_value)

• trimspec Optional. If included must be one of the keywords BOTH, LEADING or TRAILING.
• trimchar Optional. If included should be a one-character string value.
• string_value The string value to trim.

SELECT TRIM('   trimmed   ');
-- Result: 'trimmed'

SELECT TRIM(LEADING FROM '   trimmed   ');
-- Result: 'trimmed   '

SELECT TRIM('-'\` \`FROM '-----trimmed-----');
-- Result: 'trimmed'

SELECT TRIM(BOTH '-'\` \`FROM '-----trimmed-----');
-- Result: 'trimmed'

SELECT TRIM(TRAILING '-'\` \`FROM '-----trimmed-----');
-- Result: '-----trimmed'

​

UNICODE

UNICODE(ncharacter_expression)

• ncharacter_expression The Unicode character expression.

​

UPPER

UPPER(character_expression)

• character_expression The character expression.

SELECT UPPER('MIXED case');
-- Result: 'MIXED CASE'

​

XML_EXTRACT

XML_EXTRACT(xml, xpath [, separator])

• 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 The optional token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.

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'