String Functions
ASCII
Description
Returns the ASCII value of the first character in the string as an int.
ASCII(string)Parameters
string - the argument
Example
Return the ASCII for the first character in the Players' names:
select ASCII(name) FROM Players;BIT_LENGTH
Description
Returns the number of bits in a string as a long. For BLOB, CLOB, BYTES, and JAVA_OBJECT, the object’s specified precision is used. Each character needs 16 bits.
BIT_LENGTH(string)Parameters
string - the argument
Example
Return the bit length for Players' names:
select BIT_LENGTH(name) FROM Players;LENGTH
Description
Returns the number of characters in a string as a long. For BLOB, CLOB, BYTES, and JAVA_OBJECT, the object’s specified precision is used.
{LENGTH | CHAR_LENGTH | CHARACTER_LENGTH} (string)Parameters:
string - the argument
Example
Return the length of Players' names:
SELECT LENGTH(name) FROM Players;OCTET_LENGTH
Description
Returns the number of bytes in a string as a long. For BLOB, CLOB, BYTES and JAVA_OBJECT, the object’s specified precision is used. Each character needs 2 bytes.
OCTET_LENGTH(string)Parameters
string - the argument
Example
Return the number of bytes in Players' names:
SELECT OCTET_LENGTH(name) FROM Players;CHAR
Description
Returns the character that represents the ASCII value of an integer as a string.
{CHAR | CHR} (int)Parameters
int - the argument
Example
Return the ASCII representation from Players' names:
SELECT CHAR(65)||name FROM Players;CONCAT
Description
Concatenates strings. Unlike with the || operator, NULL parameters are ignored and do not cause the result to become NULL. This function returns a string.
CONCAT(string, string [,...])Parameters
string - the argument
Example
Concatenate Players' names:
SELECT CONCAT(NAME, '!') FROM Players;CONCAT_WS
Description
Concatenates strings and inserts a separator. Unlike with the || operator, NUL parameters are ignored, and do not cause the result to become NULL. This function returns a string.
CONCAT_WS(separatorString, string, string [,...])Parameters
-
separatorString- the separator to insert -
string- the argument
Example
Concatenate Players' names with comma as a separator:
SELECT CONCAT_WS(',', NAME, '!') FROM Players;DIFFERENCE
Description
Returns the difference between the SOUNDEX() values of two strings as an`int`.
DIFFERENCE(X, Y)Parameters
X and Y - strings to compare
Example
Calculate 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
Description
Converts a hex representation of a string to a string. Uses 4 hex characters per string character.
HEXTORAW(string)Parameters
string - a hex string to convert
Example
Return a string representation of Players' names:
SELECT HEXTORAW(DATA) FROM Players;RAWTOHEX
Description
Converts a string to the hex representation. Uses 4 hex characters per string character. Returns a string.
RAWTOHEX(string)Parameters
string - a string to convert
Example
Return hex representations of Players' names:
SELECT RAWTOHEX(DATA) FROM Players;INSTR
Description
Returns the location of a search string in a string. If a start position is used, the characters before it are ignored. If the position is negative, the rightmost location is returned. Zero (0) is returned if the search string is not found.
INSTR(string, searchString, [, startInt])Parameters
-
string- the string to search in -
searchString- the string to search for -
startInt- start position for the search
Example
Check if a string includes "@":
SELECT INSTR(EMAIL,'@') FROM Players;INSERT
Description
Inserts an additional string into the original string at a specified start position, returns a string.
INSERT(originalString, startInt, lengthInt, addString)Parameters:
-
originalString- the original string -
startInt- the start position -
lengthInt- the number of characters to remove at the start position in the original string -
addString- the string to insert
Example
Insert a blank space at the beginning of Players' names.
SELECT INSERT(NAME, 1, 1, ' ') FROM Players;LOWER
Description
Converts a string to lowercase.
{LOWER | LCASE} (string)Parameters
string - the string to convert
Example
Convert to lower case Players' names:
SELECT LOWER(NAME) FROM Players;UPPER
Description
Converts a string to uppercase.
{UPPER | UCASE} (string)Parameters
string - the string to convert
Example
Return the last name for each Player in uppercase:
SELECT UPPER(last_name) "LastNameUpperCase" FROM Players;LEFT
Description
Returns the specified number of leftmost characters in a string.
LEFT(string, int)Parameters
-
string- the string to extract the characters from -
int- the number of characters to extract
Example
Extract the first 3 letters from Players' names:
SELECT LEFT(NAME, 3) FROM Players;RIGHT
Returns the specified number of rightmost characters in a string.
RIGHT(string, int)Parameters
-
string- the string to extract the characters from -
int- the number of characters to extract
Example
Extract the first 3 letters from Players' names:
SELECT RIGHT(NAME, 3) FROM Players;LOCATE
Description
Returns the location of a search string in a string. If a start position is used, the characters before it are ignored. If the position is negative, the rightmost location is returned. Zero (0) is returned if the search string is not found.
LOCATE(searchString, string [, startInt])Parameters
-
searchString- the string to search for -
string- the string to search in -
startInt- start position for the search
Example
Search for period (.) in Players' names:
SELECT LOCATE('.', NAME) FROM Players;POSITION
Description
Returns the location of a search string in a string. A simplified version of LOCATE.
POSITION(searchString, string)Parameters
-
searchString- the string to search for -
string- the string to search in
Example
Search for period (.) in Players' names:
SELECT POSITION('.', NAME) FROM Players;LPAD
Description
Left-pads the string to the specified length. If the length is shorter than the string, the string is truncated at the end. If the padding string is not set, spaces are used.
LPAD(string, int[, paddingString])Parameters
-
string- the string to pad -
int- the length to pad to -
paddingString- the string to pad with
Example
Left-pad Players' amount with asterisks to the length of 10:
SELECT LPAD(AMOUNT, 10, '*') FROM Players;RPAD
Description
Right-pad the string to the specified length. If the length is shorter than the string, the string is truncated. If the padding string is not set, spaces are used.
RPAD(string, int[, paddingString])Parameters
-
string- the string to pad -
int- the length to pad to -
paddingString- the string to pad with
Example
Right-pad Players' text with dashed to the length of 10:
SELECT RPAD(TEXT, 10, '-') FROM Players;LTRIM
Description
Removes all leading spaces from a string.
LTRIM(string)Parameters
string - the string to left-trim
Example
Remove leading spaces from Players' names:
SELECT LTRIM(NAME) FROM Players;RTRIM
Description
Removes all trailing spaces from a string.
RTRIM(string)Parameters
string - the string to right-trim
Example
Remove trailing spaces from Players' names:
SELECT RTRIM(NAME) FROM Players;TRIM
Description
Removes all leading spaces, trailing spaces, or spaces at both ends, from a string. Other characters can be removed as well.
TRIM ([{LEADING | TRAILING | BOTH} [string] FROM] string)Parameters
-
LEADING | TRAILING | BOTH- indicates where to trim -
[string]- (optional) what character(s)/string to trim -
string- the string to trim
Example
Trim underscore characters on both ends of Players' names:
SELECT TRIM(BOTH '_' FROM NAME) FROM Players;REGEXP_REPLACE
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.
REGEXP_REPLACE(inputString, regexString, replacementString [, flagsString])Parameters
-
inputString- the string to replace substrings in -
regexString- the regular expression to match -
replacementString- the string to replace the matching substrings with -
flagString- possible values are:-
'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)
Other symbols cause an exception. Multiple symbols can be used in the
flagsStringparameter; for example, 'im'. Later flags override former ones; for example, 'ic' is equivalent to case sensitive, matching 'c'.
-
Example
Returns the names from the Players table with 'w' replaced by 'W':
SELECT REGEXP_REPLACE(name, 'w+', 'W', 'i') FROM Players;REGEXP_LIKE
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.
REGEXP_LIKE(inputString, regexString [, flagsString])Parameters
-
inputString- the string to match regular expression -
regexString- the regular expression to match -
flagString- possible values are:-
'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)
Other symbols cause an exception. Multiple symbols can be used in the
flagsStringparameter; for example, 'im'. Later flags override former ones; for example, 'ic' is equivalent to case sensitive, matching 'c'.
-
Example
Returns Players whose names match 'A-M':
SELECT REGEXP_LIKE(name, '[A-M ]*', 'i') FROM Players;REPEAT
Description
Returns a string repeated the specified number of times.
REPEAT(string, int)Parameters
-
string- the string to repeat -
int- the number of times to repeat the string
Example
Repeat Players' names 10 times:
SELECT REPEAT(NAME || ' ', 10) FROM Players;REPLACE
Description
Replaces all occurrences of a search string in the specified text with another string. If no replacement is specified, the search string is removed from the text. If any parameter is NULL, the result is NULL.
REPLACE(string, searchString [, replacementString])Parameters
-
string- the text to replace the string in -
searchString- the string to replace -
replacementString- the string to replace with
Example
Remove spaces from Players' names:
SELECT REPLACE(NAME, ' ') FROM Players;SOUNDEX
Description
Returns a four-character code representing the SOUNDEX of a string. For details, see http://www.archives.gov/genealogy/census/soundex.html. Returns a string.
SOUNDEX(string)Parameters
string - the SOUNDEX source string
Example
Return the four-character code representing the SOUNDEX of Players' names:
SELECT SOUNDEX(NAME) FROM Players;SPACE
Description
Returns a string consisting of the specified number of spaces.
SPACE(int)Parameters
int - the number of spaces in the string to return
Example
Return two columns: Players' names in one, 80 spaces in the other:
SELECT name, SPACE(80) FROM Players;STRINGDECODE
Description
Converts an encoded string using the Java string literal encoding format. Special characters are \b, \t, \n, \f, \r, \", \, \<octal>, \u<unicode>. Returns a string.
STRINGDECODE(string)Parameters
string - the string that contains the special characters
Example
Convert the line break symbol (\n) to a format Java can work with:
STRINGDECODE('Lines 1\nLine 2');STRINGENCODE
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>. Returns a string.
STRINGENCODE(string)Parameters
string - the string that contains the special characters
Example
Encode the previously converted line break symbol (\n):
STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))STRINGTOUTF8
Description
Encodes a string to a byte array using the UTF8 encoding format. Returns bytes.
STRINGTOUTF8(string)Parameters
string - the string to encode
Example
Encode Players' names:
SELECT STRINGTOUTF8(name) FROM Players;SUBSTRING
Description
Returns a substring of a string starting at the specified position. Also supported is: SUBSTRING(string [FROM start] [FOR length]).
{SUBSTRING | SUBSTR} (string, startInt [, lengthInt])
SUBSTRING(string [FROM start] [FOR length])Parameters
-
string- the string to extract the substring from -
startInt|start- the starting position for the substring to extract; if negative, the start index is relative to the end of the string -
lengthInt|length- (optional) the length of the substring to extract
Example
From Players names, extract a 5-character long substring that starts at the second position:
SELECT SUBSTR(name, 2, 5) FROM Players;TO_CHAR
Description
Formats a timestamp, number, or text.
TO_CHAR(value [, formatString[, nlsParamString]])Parameters
-
formatString- the string to be formatted -
nlsParamString- the format to use
Example:
Format the '2010-01-01 00:00:00' timestamp to 'DD MON, YYYY':
TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY')TRANSLATE
Description
Replaces a sequence of characters in a string with another set of characters.
TRANSLATE(value , searchString, replacementString]])Parameters
-
value- the string that contains the character sequence(s) -
searchString- the sequence of characters to replace -
replacementString- the string to replace the sequence of characters with
Example
Replace 'el' from 'Hello world!' with 'EL':
TRANSLATE('Hello world', 'el', 'EL')UTF8TOSTRING
Description
Decodes a byte array in the UTF8 format to a string.
UTF8TOSTRING(bytes)Parameters
bytes - the array to decode
Example
Decode byte array derived from Players' names:
SELECT UTF8TOSTRING(name) FROM Players;XMLATTR
Description
Creates an XML attribute element in the name=value form. The value is encoded as XML text. Returns a string.
XMLATTR(nameString, valueString)Parameters
-
nameString- the 'name' part of the element -
valueString- the 'value' part of the element
Example
Create the 'href=http://h2database.com' XML element:
XMLNODE('a', XMLATTR('href', 'http://h2database.com'))XMLNODE
Description
Creates 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 function returns a string.
XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])Parameters
-
elementString- the name of the element/node to create -
attributeString- the list of attributes for the element/node to create -
contentString- the element/node content -
indentBoolean- 'yes' to indent the node element, 'no' otherwise
Example
Create 'a' XML node element at the H2 indentation level:
XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2')XMLCOMMENT
Description
Creates an XML comment. Two dashes (--) are converted to - -. This function returns a string.
XMLCOMMENT(commentString)Parameters
commentString - the comment content
Example
Create the 'Test' comment:
XMLCOMMENT('Test')XMLCDATA
Description
Creates an XML CDATA element. If the value contains ]]>, an XML text element is created instead. This function returns a string.
XMLCDATA(valueString)Parameters
valueString - the CDATA/text element’s content
Example
Create the 'data' CDATA element:
XMLCDATA('data')XMLSTARTDOC
Description
Returns the XML declaration. The result is always <?xml version=1.0?>.
XMLSTARTDOC()Example
XMLSTARTDOC()XMLTEXT
Description
Creates an XML text element, returns a string.
XMLTEXT(valueString [, escapeNewlineBoolean])Parameters
-
valueString- the text element’s content -
escapeNewlineBoolean- if 'yes', newline and linefeed are converted to an XML entity (&#)
Example
Create the 'data' XML text element:
XMLTEXT(('data'))© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.