String Functions | GridGain Documentation

ASCII

ASCII(string)

Parameters

string - an argument.

Description

Return the ASCII value of the first character in the string. This method returns an int.

Example

select ASCII(name) FROM Players;

BIT_LENGTH

BIT_LENGTH(string)

Parameters

string - an argument.

Description

Returns the number of bits in a string. This method returns a long. For BLOB, CLOB, BYTES, and JAVA_OBJECT, the object’s specified precision is used. Each character needs 16 bits.

Example

select BIT_LENGTH(name) FROM Players;

LENGTH

{LENGTH | CHAR_LENGTH | CHARACTER_LENGTH} (string)

Parameters

string - an argument.

Description

Returns the number of characters in a string. This method returns a long. For BLOB, CLOB, BYTES, and JAVA_OBJECT, the object’s specified precision is used.

Example

SELECT LENGTH(name) FROM Players;

OCTET_LENGTH

OCTET_LENGTH(string)

Parameters

string - an argument.

Description

Returns the number of bytes in a string. This method returns a long. For BLOB, CLOB, BYTES and JAVA_OBJECT, the object’s specified precision is used. Each character needs 2 bytes.

Example

SELECT OCTET_LENGTH(name) FROM Players;

CHAR

{CHAR | CHR} (int)

Parameters

int - an argument.

Description

Returns the character that represents the ASCII value. This method returns a string.

Example

SELECT CHAR(65)||name FROM Players;

CONCAT

CONCAT(string, string [,...])

Parameters

string - an argument.

Description

Combines strings. Unlike with the || operator, NULL parameters are ignored and do not cause the result to become NULL. This method returns a string.

Example

SELECT CONCAT(NAME, '!') FROM Players;

CONCAT_WS

CONCAT_WS(separatorString, string, string [,...])

Parameters

separatorString - separator.
string - an argument.

Description

Combines strings, dividing with a separator. Unlike with the || operator, NUL parameters are ignored, and do not cause the result to become NULL. This method returns a string.

Example

SELECT CONCAT_WS(',', NAME, '!') FROM Players;

DIFFERENCE

DIFFERENCE(X, Y)

Parameters

X, Y - strings to compare.

Description

Returns the difference between the SOUNDEX() values of two strings. This method returns an int.

Example

Calculates the SOUNDEX() difference for two Players' names:

select DIFFERENCE(T1.NAME, T2.NAME) FROM players T1, players T2
   WHERE T1.ID = 10 AND T2.ID = 11;

HEXTORAW

HEXTORAW(string)

Parameters

string - a hex string to use for the conversion.

Description

Converts a hex representation of a string to a string. 4 hex characters per string character are used.

Example

Calculate a harmony for Players' names:

SELECT HEXTORAW(DATA) FROM Players;

RAWTOHEX

RAWTOHEX(string)

Parameters

string - a string to convert to the hex representation.

Description

Converts a string to the hex representation. 4 hex characters per string character are used. This method returns a string.

Example

Calculate a harmony for Players' names:

SELECT RAWTOHEX(DATA) FROM Players;

INSTR

INSTR(string, searchString, [, startInt])

Parameters

string - any string.
searchString - any string to search for.
startInt - start position for the lookup.

Description

Returns the location of a search string in a string. If a start position is used, the characters before it are ignored. If position is negative, the rightmost location is returned. 0 is returned if the search string is not found. Please note this function is case sensitive, even if the parameters are not.

Example

Check if a string includes the "@" symbol:

SELECT INSTR(EMAIL,'@') FROM Players;

INSERT

INSERT(originalString, startInt, lengthInt, addString)

Parameters

originalString - an original string.
startInt - start position.
lengthInt - the length.
addString - an additional string.

Description

Inserts a additional string into the original string at a specified start position. The length specifies the number of characters that are removed at the start position in the original string. This method returns a string.

Example

SELECT INSERT(NAME, 1, 1, ' ') FROM Players;

LOWER

{LOWER | LCASE} (string)

Parameters

string - an argument.

Description

Converts a string to lowercase.

Example

SELECT LOWER(NAME) FROM Players;

UPPER

{UPPER | UCASE} (string)

Parameters

string - an argument.

Description

Converts a string to uppercase.

Examples

The following example returns the last name in uppercase for each Player:

SELECT UPPER(last_name) "LastNameUpperCase" FROM Players;

LEFT

LEFT(string, int)

Parameters

string - an argument.
int - a number of characters to extract.

Description

Returns the leftmost number of characters.

Examples

Get 3 first letters of Players' names:

SELECT LEFT(NAME, 3) FROM Players;

RIGHT

RIGHT(string, int)

Parameters

string - an argument.
int - a number of characters to extract.

Description

Returns the rightmost number of characters.

Examples

Get the last 3 letters of Players' names:

SELECT RIGHT(NAME, 3) FROM Players;

LOCATE

LOCATE(searchString, string [, startInt])

Description

Returns the location of a search string in a string. If a start position is used, the characters before it are ignored. If position is negative, the rightmost location is returned. 0 is returned if the search string is not found.

Examples

SELECT LOCATE('.', NAME) FROM Players;

POSITION

POSITION(searchString, string)

Description

Returns the location of a search string in a string. See also LOCATE.

Examples

SELECT POSITION('.', NAME) FROM Players;

LPAD

LPAD(string, int[, paddingString])

Description

Left pad the string to the specified length. If the length is shorter than the string, it will be truncated at the end. If the padding string is not set, spaces will be used.

Examples

SELECT LPAD(AMOUNT, 10, '*') FROM Players;

RPAD

RPAD(string, int[, paddingString])

Description

Right pad the string to the specified length. If the length is shorter than the string, it will be truncated. If the padding string is not set, spaces will be used.

Examples

SELECT RPAD(TEXT, 10, '-') FROM Players;

LTRIM

LTRIM(string)

Description

Removes all leading spaces from a string.

Examples

SELECT LTRIM(NAME) FROM Players;

RTRIM

RTRIM(string)

Description

Removes all trailing spaces from a string.

Examples

SELECT RTRIM(NAME) FROM Players;

TRIM

TRIM ([{LEADING | TRAILING | BOTH} [string] FROM] string)

Description

Removes all leading spaces, trailing spaces, or spaces at both ends, from a string. Other characters can be removed as well.

Examples

SELECT TRIM(BOTH '_' FROM NAME) FROM Players;

REGEXP_REPLACE

REGEXP_REPLACE(inputString, regexString, replacementString [, flagsString])

Description

Replaces each substring that matches a regular expression. For details, see the Java String.replaceAll() method. If any parameter is null (except the optional flagsString parameter), the result is null.

Flags values are limited to 'i', 'c', 'n', 'm'. Other symbols cause an exception. Multiple symbols can be used in one flagsString parameter (for example: 'im'). Later flags override earlier ones, for example 'ic' is equivalent to case sensitive, matching 'c'.

'i' enables case insensitive matching (Pattern.CASE_INSENSITIVE)
'c' disables case insensitive matching (Pattern.CASE_INSENSITIVE)
'n' allows the period to match the newline character (Pattern.DOTALL)
'm' enables multiline mode (Pattern.MULTILINE)

Examples

SELECT REGEXP_REPLACE(name, 'w+', 'W', 'i') FROM Players;

REGEXP_LIKE

REGEXP_LIKE(inputString, regexString [, flagsString])

Description

Matches string to a regular expression. For details, see the Java Matcher.find() method. If any parameter is null (except the optional flagsString parameter), the result is null.

Flags values are limited to 'i', 'c', 'n', 'm'. Other symbols cause an exception. Multiple symbols can be used in one flagsString parameter (for example: 'im'). Later flags override earlier ones, for example 'ic' is equivalent to case sensitive, matching 'c'.

'i' enables case insensitive matching (Pattern.CASE_INSENSITIVE)
'c' disables case insensitive matching (Pattern.CASE_INSENSITIVE)
'n' allows the period to match the newline character (Pattern.DOTALL)
'm' enables multiline mode (Pattern.MULTILINE)

Examples

SELECT REGEXP_LIKE(name, '[A-Z ]*', 'i') FROM Players;

REPEAT

REPEAT(string, int)

Description

Returns a string repeated some number of times.

Examples

SELECT REPEAT(NAME || ' ', 10) FROM Players;

REPLACE

REPLACE(string, searchString [, replacementString])

Description

Replaces all occurrences of a search string in specified text with another string. If no replacement is specified, the search string is removed from the original string. If any parameter is null, the result is null.

Examples

SELECT REPLACE(NAME, ' ') FROM Players;

SOUNDEX

SOUNDEX(string)

Description

Returns a four character code representing the SOUNDEX of a string. See also http://www.archives.gov/genealogy/census/soundex.html. This method returns a string.

Examples

SELECT SOUNDEX(NAME) FROM Players;

SPACE

SPACE(int)

Description

Returns a string consisting of the specified number of spaces.

Examples

SELECT name, SPACE(80) FROM Players;

STRINGDECODE

STRINGDECODE(string)

Description

Converts an encoded string using the Java string literal encoding format. Special characters are \b, \t, \n, \f, \r, \", \, \<octal>, \u<unicode>. This method returns a string.

Examples

STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'));

STRINGENCODE

STRINGENCODE(string)

Description

Encodes special characters in a string using the Java string literal encoding format. Special characters are \b, \t, \n, \f, \r, \", \, \<octal>, \u<unicode>. This method returns a string.

Examples

STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))

STRINGTOUTF8

STRINGTOUTF8(string)

Description

Encodes a string to a byte array using the UTF8 encoding format. This method returns bytes.

Examples

SELECT UTF8TOSTRING(STRINGTOUTF8(name)) FROM Players;

SUBSTRING

{SUBSTRING | SUBSTR} (string, startInt [, lengthInt])

Description

Returns a substring of a string starting at the specified position. If the start index is negative, then the start index is relative to the end of the string. The length is optional. Also supported is: SUBSTRING(string [FROM start] [FOR length]).

Examples

SELECT SUBSTR(name, 2, 5) FROM Players;

UTF8TOSTRING

UTF8TOSTRING(bytes)

Description

Decodes a byte array in UTF8 format to a string.

Examples

SELECT UTF8TOSTRING(STRINGTOUTF8(name)) FROM Players;

XMLATTR

XMLATTR(nameString, valueString)

Description

Creates an XML attribute element of the form name=value. The value is encoded as XML text. This method returns a string.

Examples

XMLNODE('a', XMLATTR('href', 'http://h2database.com'))

XMLNODE

XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])

Description

Create an XML node element. An empty or null attribute string means no attributes are set. An empty or null content string means the node is empty. The content is indented by default if it contains a newline. This method returns a string.

Examples

XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2')

XMLCOMMENT

XMLCOMMENT(commentString)

Description

Creates an XML comment. Two dashes (--) are converted to - -. This method returns a string.

Examples

XMLCOMMENT('Test')

XMLCDATA

XMLCDATA(valueString)

Description

Creates an XML CDATA element. If the value contains ]]>, an XML text element is created instead. This method returns a string.

Examples

XMLCDATA('data')

XMLSTARTDOC

XMLSTARTDOC()

Description

Returns the XML declaration. The result is always <?xml version=1.0?>.

Examples

XMLSTARTDOC()

XMLTEXT

XMLTEXT(valueString [, escapeNewlineBoolean])

Description

Creates an XML text element. If enabled, newline and linefeed is converted to an XML entity (&#). This method returns a string.

Examples

XMLSTARTDOC()

TO_CHAR

TO_CHAR(value [, formatString[, nlsParamString]])

Description

Formats a timestamp, number, or text.

Examples

TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY')

TRANSLATE

TRANSLATE(value , searchString, replacementString]])

Description

Replaces a sequence of characters in a string with another set of characters.

Examples

TRANSLATE('Hello world', 'eo', 'EO')