= String Functions

:toclevels:

== ASCII

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

[source,sql]
----
ASCII(string)
----


Parameters:
- `string` - an argument.


Example:

[source,sql]
----
select ASCII(name) FROM Players;
----


== BIT_LENGTH
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.


[source,sql]
----
BIT_LENGTH(string)
----

Parameters:
- `string` - an argument.


Example:

[source,sql]
----
select BIT_LENGTH(name) FROM Players;
----


== LENGTH
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.


[source,sql]
----
{LENGTH | CHAR_LENGTH | CHARACTER_LENGTH} (string)
----


Parameters:
- `string` - an argument.


Example:

[source,sql]
----
SELECT LENGTH(name) FROM Players;
----


== OCTET_LENGTH
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.


[source,sql]
----
OCTET_LENGTH(string)
----


Parameters:
- `string` - an argument.


Example:

[source,sql]
----
SELECT OCTET_LENGTH(name) FROM Players;
----


== CHAR

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

[source,sql]
----
{CHAR | CHR} (int)
----


Parameters:
- `int` - an argument.


Example:

[source,sql]
----
SELECT CHAR(65)||name FROM Players;
----


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


[source,sql]
----
CONCAT(string, string [,...])
----


Parameters:
- `string` - an argument.


Example:

[source,sql]
----
SELECT CONCAT(NAME, '!') FROM Players;
----


== CONCAT_WS
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.


[source,sql]
----
CONCAT_WS(separatorString, string, string [,...])
----


Parameters:
- `separatorString` - separator.
- `string` - an argument.


Example:

[source,sql]
----
SELECT CONCAT_WS(',', NAME, '!') FROM Players;
----


== DIFFERENCE

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

[source,sql]
----
DIFFERENCE(X, Y)
----


Parameters:
- `X`, `Y` - strings to compare.


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


[source,sql]
----
select DIFFERENCE(T1.NAME, T2.NAME) FROM players T1, players T2
   WHERE T1.ID = 10 AND T2.ID = 11;
----


== HEXTORAW

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

[source,sql]
----
HEXTORAW(string)
----


Parameters:
- `string` - a hex string to use for the conversion.


Example:
Calculate a harmony for Players' names:


[source,sql]
----
SELECT HEXTORAW(DATA) FROM Players;
----


== RAWTOHEX

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

[source,sql]
----
RAWTOHEX(string)
----

Parameters:
- `string` - a string to convert to the hex representation.


Example:
Calculate a harmony for Players' names:


[source,sql]
----
SELECT RAWTOHEX(DATA) FROM Players;
----


== INSTR

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.


[source,sql]
----
INSTR(string, searchString, [, startInt])
----


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


Example:
Check if a string includes the "@" symbol:


[source,sql]
----
SELECT INSTR(EMAIL,'@') FROM Players;
----


== INSERT

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

[source,sql]
----
INSERT(originalString, startInt, lengthInt, addString)
----

Parameters:

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


Example:

[source,sql]
----
SELECT INSERT(NAME, 1, 1, ' ') FROM Players;
----


== LOWER

Converts a string to lowercase.

[source,sql]
----
{LOWER | LCASE} (string)
----


Parameters:
- `string` - an argument.


Example:

[source,sql]
----
SELECT LOWER(NAME) FROM Players;
----


== UPPER

Converts a string to uppercase.

[source,sql]
----
{UPPER | UCASE} (string)
----


Parameters:
- `string` - an argument.


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


[source,sql]
----
SELECT UPPER(last_name) "LastNameUpperCase" FROM Players;
----


== LEFT

Returns the leftmost number of characters.

[source,sql]
----
LEFT(string, int)
----


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


Example:
Get 3 first letters of Players' names:


[source,sql]
----
SELECT LEFT(NAME, 3) FROM Players;
----


== RIGHT

Returns the rightmost number of characters.

[source,sql]
----
RIGHT(string, int)
----


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


Example:
Get the last 3 letters of Players' names:


[source,sql]
----
SELECT RIGHT(NAME, 3) FROM Players;
----


== LOCATE

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.

[source,sql]
----
LOCATE(searchString, string [, startInt])
----


Example:

[source,sql]
----
SELECT LOCATE('.', NAME) FROM Players;
----


== POSITION

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

[source,sql]
----
POSITION(searchString, string)
----


Example:

[source,sql]
----
SELECT POSITION('.', NAME) FROM Players;
----


== LPAD

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.

[source,sql]
----
LPAD(string, int[, paddingString])
----


Example:

[source,sql]
----
SELECT LPAD(AMOUNT, 10, '*') FROM Players;
----


== RPAD

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.

[source,sql]
----
RPAD(string, int[, paddingString])
----


Example:

[source,sql]
----
SELECT RPAD(TEXT, 10, '-') FROM Players;
----


== LTRIM

Removes all leading spaces from a string.

[source,sql]
----
LTRIM(string)
----


Example:

[source,sql]
----
SELECT LTRIM(NAME) FROM Players;
----


== RTRIM

Removes all trailing spaces from a string.

[source,sql]
----
RTRIM(string)
----


Example:

[source,sql]
----
SELECT RTRIM(NAME) FROM Players;
----


== TRIM

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

[source,sql]
----
TRIM ([{LEADING | TRAILING | BOTH} [string] FROM] string)
----


Example:

[source,sql]
----
SELECT TRIM(BOTH '_' FROM NAME) FROM Players;
----


== REGEXP_REPLACE

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.

[source,sql]
----
REGEXP_REPLACE(inputString, regexString, replacementString [, flagsString])
----


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)


Example:

[source,sql]
----
SELECT REGEXP_REPLACE(name, 'w+', 'W', 'i') FROM Players;
----


== REGEXP_LIKE

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.

[source,sql]
----
REGEXP_LIKE(inputString, regexString [, flagsString])
----


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)


Example:

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


== REPEAT

Returns a string repeated some number of times.

[source,sql]
----
REPEAT(string, int)
----


Example:

[source,sql]
----
SELECT REPEAT(NAME || ' ', 10) FROM Players;
----


== REPLACE

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.

[source,sql]
----
REPLACE(string, searchString [, replacementString])
----


Example:

[source,sql]
----
SELECT REPLACE(NAME, ' ') FROM Players;
----


== SOUNDEX

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

[source,sql]
----
SOUNDEX(string)
----


Example:

[source,sql]
----
SELECT SOUNDEX(NAME) FROM Players;
----


== SPACE

Returns a string consisting of the specified number of spaces.

[source,sql]
----
SPACE(int)
----


Example:


[source,sql]
----
SELECT name, SPACE(80) FROM Players;
----


== STRINGDECODE

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

[source,sql]
----
STRINGDECODE(string)
----

Example:

[source,sql]
----
STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'));
----


== STRINGENCODE

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

[source,sql]
----
STRINGENCODE(string)
----


Example:

[source,sql]
----
STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))
----


== STRINGTOUTF8

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

[source,sql]
----
STRINGTOUTF8(string)
----


Example:

[source,sql]
----
SELECT UTF8TOSTRING(STRINGTOUTF8(name)) FROM Players;
----


== SUBSTRING

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])`.

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


Example:

[source,sql]
----
SELECT SUBSTR(name, 2, 5) FROM Players;
----


== UTF8TOSTRING

Decodes a byte array in UTF8 format to a string.

[source,sql]
----
UTF8TOSTRING(bytes)
----


Example:

[source,sql]
----
SELECT UTF8TOSTRING(STRINGTOUTF8(name)) FROM Players;
----


== XMLATTR

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

[source,sql]
----
XMLATTR(nameString, valueString)
----


Example:

[source,sql]
----
XMLNODE('a', XMLATTR('href', 'http://h2database.com'))
----


== XMLNODE

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

[source,sql]
----
XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])
----


Example:

[source,sql]
----
XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2')
----


== XMLCOMMENT

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

[source,sql]
----
XMLCOMMENT(commentString)
----


Example:

[source,sql]
----
XMLCOMMENT('Test')
----


== XMLCDATA

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

[source,sql]
----
XMLCDATA(valueString)
----

Example:

[source,sql]
----
XMLCDATA('data')
----


== XMLSTARTDOC

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

[source,sql]
----
XMLSTARTDOC()
----


Example:

[source,sql]
----
XMLSTARTDOC()
----


== XMLTEXT


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

[source,sql]
----
XMLTEXT(valueString [, escapeNewlineBoolean])
----


Example:

[source,sql]
----
XMLSTARTDOC()
----


== TO_CHAR

Formats a timestamp, number, or text.

[source,sql]
----
TO_CHAR(value [, formatString[, nlsParamString]])
----


Example:

[source,sql]
----
TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY')
----


== TRANSLATE

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

[source,sql]
----
TRANSLATE(value , searchString, replacementString]])
----


Example:

[source,sql]
----
TRANSLATE('Hello world', 'eo', 'EO')
----