Page History

...

SELECT RTRIM(Fname),City FROM Employee

Back to top

TRIM

TRIM([option] [trimString] [FROM] string)

...

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

Example

SELECT TRIM(BOTH '_' FROM Fname) FROM Employee

Back to top

REPEAT

REPEAT(string, int)

Returns a string repeated some number of times.

Example

SELECT REPEAT (Fname,3) FROM Employee WHERE Fname LIKE'T%'

...

REPLACE

REPLACE(string, searchString [, replacementString])

Replaces all occurrences of a search string in a 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.

Example

SELECT REPLACE(Fname, ' ') FROM Employee 

Back to top

SUBSTRING, SUBSTR

SUBSTRING(string, startint [, lengthint])

Returns a substring of a string starting at a 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]).

Example

SELECT SUBSTR('[Hello]', 1, 3) FROM Employee 

...

Back to top

TO_CHAR

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

TO_CHAR function is used to format a timestamp, a number, or text.

Example

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

...

SPACE

Returns a string consisting of a number of spaces.

Example

SELECT E_id,Fname,space(10),City FROM Employee 

Back to top

STRINGDECODE

Converts a 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.

Example

SELECT STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) FROM Employee 

...

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.

Example

SELECT STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) FROM Employee 

...

Back to top

STRINGTOUTF8

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

Example

SELECT UTF8TOSTRING(STRINGTOUTF8('This is a test')) FROM Employee 

...

Back to top

UTF8TOSTRING

Decodes a byte array in the UTF8 format to a string.

Example

SELECT UTF8TOSTRING(STRINGTOUTF8('This is a test')) FROM Employee 

...

Back to top

XMLATTRXMLATTR

XMLATTR(nameString, valueString)

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

Example

SELECT XMLNODE('a', XMLATTR('p_name', 'http://h2database.com')) FROM Employee 

...

Back to top

XMLNODE

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

Create an XML node element.

Example

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

...

Back to top

XMLCOMMENT

XMLCOMMENT(commentString)

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

Example

SELECT XMLCOMMENT('Comment on XML') FROM Employee 

...

XMLCDATA

XMLCDATA(valueString)

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

Example

SELECT XMLCDATA('data') FROM Employee 

...

Back to top

XMLSTARTDOC

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

Example

SELECT XMLSTARTDOC() FROM Employee 

...

XMLTEXT

XMLTEXT(valueString [,escapeNewlineBoolean])

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

Example

SELECT XMLTEXT('Text data') FROM Employee 

...

Back to top

Time and Date Functions (New in 17.0)

...

Returns the current date.

Example

SELECT CURRENT_DATE() FROM Employee

...

CURRENT_TIME

Returns the current time.

Example

SELECT CURRENT_TIME() FROM Employee

Back to top

CURRENT_TIMESTAMP

Returns the current timestamp.

Example

SELECT CURRENT_TIMESTAMP() FROM Employee 

...

DATEADD

DATEADD(unitString, addInt, timestamp)

Adds units to a timestamp. The string indicates the unit. Use negative values to subtract units.

Example

SELECT DATEADD('MONTH', 1, DATE '2001-01-31') FROM Employee 

Back to top

DATEDIFF

DATEDIFF(unitString, timestamp1, timestamp2)

Returns the the number of crossed unit boundaries between two timestamps.

Example

SELECT DATEDIFF('YEAR', '2005-01-31', '2010-01-31') FROM Employee 

Back to top

DAYNAME

Returns the name of the day (in English).

Example

SELECT DAYNAME('2001-01-31') FROM Employee

...

DAY_OF_MONTH

Returns the day of month (1-31).
 ExampleExample

SELECT DAY_OF_MONTH('2001-01-31') FROM Employee 

...

DAY_OF_WEEK

Returns the day of the week (1 means Sunday).

Example

SELECT DAY_OF_WEEK('2001-01-31') FROM Employee 

Back to top

DAY_OF_YEAR

Returns the day of the year (1-366).

Example

SELECT DAY_OF_YEAR('2001-01-31') FROM Employee 

Back to top

FORMATDATETIME

FORMATDATETIME(timestamp, formatString [, localeString [, timezoneString]])

Formats a date, time or timestamp as a string. The most important format characters are: y year, M month, d day, H hour, m minute, s second. Example: FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06','EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')

Example

SELECT FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT') FROM Employee

HOUR, MINUTE, MONTH, YEAR

...