SQL Reference

Character / Binary Functions

All string functions support Unicode characters. Functions that transform character case are locale sensitive.

These functions support the UTF-8 format:

  • RTRIM
  • LTRIM
  • LEFT
  • RIGHT
  • TRIM
  • TRANSLATE
  • RPAD
  • LPAD
  • SUBSTRING

ASCII

Returns the ASCII code value of the leftmost character of the character value.

The ASCII function only supports ASCII characters in the input argument.

Syntax

SQL


Example

SQL


Output: 97

Example

SQL


Output: 98

BIT_LENGTH

Returns the length of the character value in bits.

Syntax

SQL


Example

SQL


Output: 8

Example

SQL


Output: 16

Example

SQL


Output: 24

BTRIM

Alias for TRIM.

CHR

Converts an integer value to a string.

The value is first sign-extended to 8 bytes. Then, if it can be represented as 1 byte, a string is returned with that one byte.

Otherwise, a string is returned of length 2 bytes, 4 bytes, or 8 bytes with the bytes set to the big-endian representation of the integer, depending on how many high order zero bytes there are in the integer.

Syntax

SQL


Example

SQL


Output: a

CHAR_LENGTH

Alias for LENGTH.

CHARACTER_LENGTH

Alias for LENGTH.

CONCAT

Concatenates two values, which must both be either binary, hash, or string data types.

For strings, as long as one argument is a character value, the other argument is implicitly cast to a character value.

This function is equivalent to the || operator.

Syntax

SQL


Parameter

Data Type

Description

value1

BINARY, HASH or CHAR

A value to concatenate with value2. If one argument is a character value, the other is also cast to a string.

value2

BINARY, HASH or CHAR

A value to concatenate with value1. If one argument is a character value, the other is also cast to a string.

Example

SQL


Output: ocient data warehouse

Example

SQL


Output: ocient data warehouse

ENDSWITH

Returns true if x ends with y and false otherwise.

Syntax

SQL


Parameter

Data Type

Description

char1

CHAR

A string to evaluate for whether it ends with char2.

char2

CHAR

A string to evaluate for whether char1 ends with it as a substring.

Example

SQL


Output: true

Example

SQL


Output: false

Example

SQL


Output: true

INITCAP

For each word in the provided string, capitalize the first character if it is alphabetic. Words are defined as alphanumeric strings separated by non-alphanumeric characters. All other alphabetic characters are made lowercase.

Syntax

SQL


Example

SQL


Output: Ocient

INSTR

Returns the index position of the first occurrence where the character value char_substring appears in the character value char by ignoring case.

Syntax

SQL


Parameter

Data Type

Description

char

CHAR

A string to evaluate for the first index position where char_substring appears.

char_substring

CHAR

A string to evaluate for where it first appears in char.

Example

SQL


Output: 1

SQL


Output: 8

LCASE

Alias for LOWER.

LEFT

Return the number of characters in the string equal of the value integer. If integer is negative, the function returns all but the last integer characters.

Syntax

SQL


Parameter

Data Type

Description

char

CHAR

A string to be modified by returning the number of characters equal to integer.

integer

INT

The number of characters to return from the char string.



If integer is negative, the function returns all but the last integer characters.

Example

SQL


Output: ocient d

Example

SQL


Output: ocient data wareho

LENGTH

Alias for CHAR_LENGTH and CHARACTER_LENGTH.

Returns the length of the value. For character data types, this value is in terms of characters. For binary data types, this value is in terms of bytes.

Syntax

SQL


Example

SQL


Output: 21

LOCATE

Alias for POSITION.

Returns the index position of the first occurrence of the character value substring in character value string.

Optionally, you can also include an additional value offset to offset the LOCATE function by the specified number of spaces.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be evaluated for the first position of substring.

substring

CHAR

A substring to be found for its first position in string.

offset

INT

Optional. A number of index positions in string to offset the search for the substring value.



This index position must be a positive integer, and it starts from the left of string.

Example

SQL


Output: 13

Example

SQL


Output: 4

Example

In this example, the index starts at position 5. This means the function skips the initial "e" in the string, and instead it returns the second e at index position 15.

SQL


Output: 15

LOWER

Alias for LCASE.

Convert string to lowercase.

Syntax

SQL


Example

SQL


Output: ocient

Example

SQL


Output: ocient

LPAD

Pad the input text to the specified length with the pad string on the left side. If text is longer than length, it is truncated to length characters. If parameter pad is not provided, the space character is used.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be padded on its left side with additional characters, so that it equals the length value.

length

INT

The number of characters for string to total.



If the string is already longer than length, the function truncates string to equal the length value.

pad_character

CHAR

Optional. A string of one or more characters to use to pad string in order to equal the length value.



If not provided, pad_character uses a whitespace character to pad.

Example

SQL


Output: ababababaocient data warehouse

Example

SQL


Output: ocient

LTRIM

Removes leading blanks from the string value string.

Alternatively, you can specify a second string value trim_character. If trim_character value is provided, the LTRIM function removes all leading instances of the trim_character value from the string.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be trimmed on its left side.

trim_character

CHAR

Optional. A string of one or more characters that all are to be trimmed from the left side of string. Each character is trimmed individually, not as a cohesive substring.



If unspecified, this value defaults to removing all whitespace from the left side.

Example

SQL


Output: ocient

Example

SQL


Output: ocient

Example

In this example, all characters "abeo " are removed from the left side of the string.

SQL


Output: cient

MD5

Returns the hexadecimal string (all lowercase) representing the md5 hash of char.

Syntax

SQL


Example

SQL


Output: 438f03cf6e9ddf8793e02db25f2d2f88

MID

Alias for SUBSTRING.

OCTET_LENGTH

Returns the length in bytes of a character or binary value.

Syntax

SQL


Example

SQL


Output: 1

Example

SQL


Output: 6

POSITION

Alias for LOCATE.

REPEAT

Repeats the character value char a number of times equal to num.

Syntax

SQL


Example

SQL


Output: aaaaa

REPLACE

Replaces all occurrences of substr_to_remove in the character value string with substr_to_replace.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be altered by replacing all instances of substr_to_remove with substr_to_replace.

substr_to_remove

CHAR

A substring to remove from string, replacing all instances with the substr_to_replace value.

substr_to_replace

CHAR

A substring to replace all instances of substr_to_remove.

Example

SQL


Output: $c$c$c$c

REVERSE

Reverse the input string.

Syntax

SQL


Example

SQL


Output: dcba

RIGHT

Return the number of trailing characters in the string equal to the value integer.

Syntax

SQL


Parameter

Data Type

Description

char

CHAR

A string to be evaluated to return the number of trailing characters equal to integer.

integer

INT

The number of characters of return from the end of the char string.



If integer is negative, the function returns all but the first integer characters.

Example

SQL


Output: arehouse

Example

SQL


Output: ent data warehouse

RPAD

Pad the input text to the specified length with the pad string on the right side. If text is longer than length, it is truncated to length characters. If parameter pad is not provided, the space character is used.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be padded on its right side with additional characters, so that it equals the length value.

length

INT

The number of characters for string to total.



If the string is already longer than length, the function truncates string to equal the length value.

pad_character

CHAR

Optional. A string of one or more characters to use to pad string in order to equal the length value.



If not provided, pad_character uses a whitespace character to pad.

Example

SQL


Output: ocient data warehouse

Example

SQL


Output: ocient data warehouseababababa

RSUBSTRING

Returns the substring from the right side of a string, based on a specified length.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be evaluated to return a substring, based on the integer value.

integer

INT

The number of characters to return from the right side of the string value.



This value must be positive.

Example

SQL


Output: data warehouse

RTRIM

Removes leading blanks from the string value string.

Alternatively, you can specify a second string value trim_character. If trim_character value is provided, the LTRIM function removes all leading instances of the trim_character value from the string.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be trimmed on its right side.

trim_character

CHAR

Optional. A string of one or more characters that all are to be trimmed from the right side of string. Each character is trimmed individually, not as a cohesive substring.

If unspecified, this value defaults to removing all whitespace from the right side.

Example

SQL


Output: ocient

Example

SQL


Output: ocient

SPACE

Returns a string of repeated spaces equal to the number value, repeat. You can join this to another string by using the CONCAT function.

Syntax

SQL


Example

SQL


Output: end

SPLIT_PART

Split the value string based on the delimiter value. The function returns a substring from the split operation based on the index value (starting from 1).

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be split based on the delimiter value.

delimiter

CHAR

The character to use as a delimiter in string.

index

INT

The index of the substring to return. The first index position starts at 1.

Example

SQL


Output: id

Example

SQL


Output: name

Example

SQL


Output: address

STARTSWITH

Returns true if string starts with substring and false otherwise.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to search.

substring

CHAR

A substring to find at the beginning of string.

Example

SQL


Output: true

Example

SQL


Output: false

STRPOS

Equivalent to using LOCATE as LOCATE(substring, string). Note the reversed argument order.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be evaluated for the first position of substring.

substring

CHAR

A substring to be found for its first position in string.

Example

SQL


Output: 17

SUBSTR

Alias for SUBSTRING.

SUBSTRING

Alias for SUBSTR and MID.

Returns the substring of a character or binary value that starts with the position specified by the second argument and that ends with the position specified by one less than the sum of the second and third arguments. When the sum of the second and third arguments is less than two, the function returns the empty string.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be truncated based on the start_position and end_position values.

start_position

INT

The starting position to return a substring. The first index position starts at 1.

end_position

INT

Optional. The number of character spaces from the start_position to include in the returned substring.



If unspecified, SUBSTRING returns all characters after the start_position.

Example

SQL


Output: data warehouse

Example

SQL


Output: data

TO_CHAR

Converts a numeric, date, or timestamp value into a CHAR date type.

SQL


Parameter

Data Type

Description

value

DATE, TIMESTAMP, or all numeric types

A numeric, date, or timestamp value to be converted into a CHAR date type.

format

CHAR

The format used for the CHAR conversion.



For information on data type formats, see the Formatting Functions page.

Example

SQL


Output: 2020-06-10

Example

SQL


Output: 13:25:14

TRANSLATE

Replaces specified characters in a provided string with a separate set of characters.

Characters specified in the char_to_remove set are replaced with characters in the char_to_replace set based on the corresponding index position.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to have specific characters transposed, based on the char_to_remove and char_to_replace values.

char_to_remove

CHAR

The characters to be removed from string.



Characters specified in the char_to_remove set are replaced with characters in the char_to_replace set based on the corresponding index position.



If char_to_remove is longer than char_to_replace, occurrences of the extra characters are removed.

char_to_replace

CHAR

The characters to replace the removed characters in char_to_remove.

Example

SQL


Output: gbhdif

TRIM

Alias for BTRIM.

Trim leading and trailing blanks from the string.

Alternatively, you can specify a second string value trim_char. If a trim_char value is provided, the TRIM function removes all leading and trailing instances of the trim_char value from the string.

Syntax

SQL


Parameter

Data Type

Description

string

CHAR

A string to be trimmed of leading and trailing whitespace, or any other characters specified by the trim_char argument.

trim_char

CHAR

Optional. If specified, this removes an alternative substring from the start or end of the string value.

Example

SQL


Output: trimmed string

Example

SQL


Output: trimmed string

UCASE

Alias for UPPER.

UPPER

Alias for UCASE.

Convert string to upper case.

Syntax

SQL


Example

SQL


Output: OCIENT