String functions for T-SQL

N/A

Differences with the UNICODE paradigm. For more information, see Collations. Syntax and option differences.

ASCII and UNICODE

Convert an ASCII or UNICODE character to its ASCII or UNICODE code.

SELECT ASCII ('A')

65

Returns a numeric integer value.

CHAR and NCHAR

Convert between ASCII or UNICODE code to a string character.

SELECT CHAR(65)

'A'

Numeric integer value as input.

CHARINDEX and PATINDEX

Find the starting position of one string expression (or string pattern) within another string expression.

SELECT CHARINDEX('ab', 'xabcdy')

2

Returns a numeric integer value.

CONCAT and CONCAT_WS

Combine multiple string input expressions into a single string with, or without, a separator character (WS).

SELECT CONCAT('a','b'), CONCAT_WS(',','a','b')

'ab', 'a,b'

LEFT, RIGHT, and SUBSTRING

Return a partial string from another string expression based on position and length.

SELECT LEFT('abs',2), SUBSTRING('abcd',2,2)

'ab', 'bc'

LOWER and UPPER

Return a string with all characters in lower or upper case. Use for presentation or to handle case insensitive expressions.

SELECT LOWER('ABcd')

'abcd'

LTRIM, RTRIM and TRIM

Remove leading and trailing spaces.

SELECT LTRIM ('abc d ')

'abc d '

STR

Convert a numeric value to a string.

SELECT STR(3.1415927,5,3)

3.142

Numeric expressions as input.

REVERSE

Return a string in reverse order.

SELECT REVERSE('abcd')

'dcba'

REPLICATE

Return a string that consists of zero or more concatenated copies of another string expression.

SELECT REPLICATE('abc', 3)

'abcabcabc'

REPLACE

Replace all occurrences of a string expression with another.

SELECT REPLACE('abcd', 'bc', 'xy')

'axyd'

STRING_SPLIT

Parse a list of values with a separator and return a set of all individual elements.

SELECT * FROM STRING_SPLIT('1,2',',') AS X©

1

2

STRING_SPLIT is a table-valued function.

STRING_AGG

Return a string that consists of concatenated string values in row groups.

SELECT STRING_AGG(C, ',') FROM VALUES(1,'a'), (1, 'b'), (2,'c') AS X (ID,C) GROUP BY I

1 'ab'

2 'c'

STRING_AGG is an aggregate function.

ASCII and ORD

Convert an ASCII or multi-byte code to its string character.

SELECT ASCII ('A')

65

Returns a numeric integer value.

CHAR

Convert between a character and its UNICODE code.

SELECT CHAR (65)

'A'

Numeric integer value as input.

LOCATE

Find the starting position of one string expression (or string pattern) within another string expression.

SELECT LOCATE ('ab', 'xabcdy')

2

Returns a numeric integer value.

CONCAT and CONCAT_WS

Combine multiple string input expressions into a single string with or without a separator character (WS).

SELECT CONCAT ('a','b'), CONCAT_WS(',','a','b')

'ab', 'a,b'

LEFT, RIGHT, and SUBSTRING

Return a partial string from another string expression based on position and length

SELECT LEFT('abs',2), SUBSTRING('abcd',2,2)

'ab', 'bc'

LOWER and UPPER

Return a string with all characters in lower or upper case. Use for presentation or to handle case insensitive expressions.

SELECT LOWER ('ABcd')

'abcd'

These have no effect when applied to binary collation strings. Convert the string to a non-binary string collation to convert letter case.

LTRIM, RTRIM, and TRIM

Remove leading and trailing spaces.

SELECT LTRIM(' abc d ')

SELECT TRIM(LEADING 'x' FROM 'xxxabcxxx')

'abc d '

'abcxxx'

TRIM in Aurora MySQL is not limited to spaces.

TRIM ([{BOTH | LEADING | TRAILING} [<Remove String>] FROM] <String>)

FORMAT

Convert a numeric value to a string.

SELECT FORMAT (3.1415927,5)

3.14159

Numeric expressions as input.

REVERSE

Return a string in reverse order.

SELECT REVERSE('abcd')

'dcba'

REPEAT

Return a string that consists of zero or more concatenated copies of another string expression.

SELECT REPEAT('abc', 3)

'abcabcabc'

REPLACE

Replace all occurrence of a string expression with another.

SELECT REPLACE('abcd', 'bc','xy')

'axyd'

ASCII and UNICODE

ASCII and ORD

Compatible. For more information, see Data Types.

CHAR and NCHAR

CHAR

Unlike SQL Server, CHAR in Aurora MySQL accepts a list of values and constructs a concatenated string. For more information, see Data Types.

CHARINDEX and PATINDEX

LOCATE and POSITION

LOCATE and POSITION are synonymous but don’t support wildcards as PATINDEX.

Use the FIND_IN_SET function to extract an element position in a comma separated value string.

CONCAT and CONCAT_WS

CONCAT and CONCAT_WS

Compatible syntax.

LEFT, RIGHT, and SUBSTRING

LEFT, RIGHT, and SUBSTRING

Compatible syntax. Aurora MySQL supports MID and SUBSTR, which are synonymous with SUBSTRING.

Use the SUBSTRING_INDEX function to extract an element from a delimited list.

LOWER and UPPER

LOWER AND UPPER

Compatible syntax. LOWER and UPPER have no effect when applied to binary collation strings.

LTRIM, RTRIM and TRIM

LTRIM, RTRIM and TRIM

Compatible syntax. TRIM in Aurora MySQL is not limited to both ends and spaces. It can be used to trim either leading or trailing characters.

The syntax is shown following:

TRIM ([{BOTH | LEADING | TRAILING} [<Remove String>] FROM] <String>)

STR

FORMAT

FORMAT doesn’t support full precision and scale definition, but does support locale formatting.

REVERSE

REVERSE

Compatible syntax.

REPLICATE

REPEAT

Compatible arguments.

REPLACE

REPLACE

Compatible syntax.

STRING_SPLIT

Not supported.

Requires iterative code to extract elements with scalar string functions.

STRING_AGG

Not supported

Requires iterative code to build a list with scalar string functions.