H2 Database EngineVersion 1.3.176 (2014-04-05)

1 of 180

Table of ContentsEmbedding H2 in an Application.....................................................................................................................................12The H2 Console Application...........................................................................................................................................12

Step-by-Step...........................................................................................................................................................12Installation.........................................................................................................................................................12Start the Console................................................................................................................................................12Login.................................................................................................................................................................13Sample..............................................................................................................................................................14Execute..............................................................................................................................................................15Disconnect.........................................................................................................................................................16End....................................................................................................................................................................16

Requirements...............................................................................................................................................................17Database Engine......................................................................................................................................................17H2 Console..............................................................................................................................................................17

Supported Platforms......................................................................................................................................................17Installing the Software..................................................................................................................................................17Directory Structure........................................................................................................................................................17Starting and Using the H2 Console.................................................................................................................................18

Firewall...................................................................................................................................................................19Testing Java............................................................................................................................................................19Error Message 'Port may be in use'...........................................................................................................................19Using another Port...................................................................................................................................................19Connecting to the Server using a Browser.................................................................................................................19Multiple Concurrent Sessions....................................................................................................................................19Login......................................................................................................................................................................20Error Messages........................................................................................................................................................20Adding Database Drivers..........................................................................................................................................20Using the H2 Console...............................................................................................................................................20Inserting Table Names or Column Names..................................................................................................................20Disconnecting and Stopping the Application...............................................................................................................20

Special H2 Console Syntax.............................................................................................................................................20Settings of the H2 Console............................................................................................................................................21Connecting to a Database using JDBC............................................................................................................................21Creating New Databases................................................................................................................................................22Using the Server...........................................................................................................................................................22

Starting the Server Tool from Command Line............................................................................................................22Connecting to the TCP Server...................................................................................................................................22Starting the TCP Server within an Application............................................................................................................22Stopping a TCP Server from Another Process.............................................................................................................22

Using Hibernate............................................................................................................................................................23Using TopLink and Glassfish..........................................................................................................................................23Using EclipseLink..........................................................................................................................................................23Using Apache ActiveMQ.................................................................................................................................................23Using H2 within NetBeans..............................................................................................................................................24Using H2 with jOOQ......................................................................................................................................................24Using Databases in Web Applications.............................................................................................................................24

Embedded Mode......................................................................................................................................................25Server Mode............................................................................................................................................................25Using a Servlet Listener to Start and Stop a Database................................................................................................25Using the H2 Console Servlet....................................................................................................................................25

Android........................................................................................................................................................................26CSV (Comma Separated Values) Support........................................................................................................................27

Reading a CSV File from Within a Database...............................................................................................................27Importing Data from a CSV File................................................................................................................................27Writing a CSV File from Within a Database................................................................................................................27Writing a CSV File from a Java Application.................................................................................................................27Reading a CSV File from a Java Application...............................................................................................................28

Upgrade, Backup, and Restore.......................................................................................................................................28Database Upgrade...................................................................................................................................................28Backup using the Script Tool....................................................................................................................................28Restore from a Script...............................................................................................................................................28Online Backup.........................................................................................................................................................28

Command Line Tools.....................................................................................................................................................29The Shell Tool...............................................................................................................................................................29Using OpenOffice Base..................................................................................................................................................30Java Web Start / JNLP...................................................................................................................................................30Using a Connection Pool................................................................................................................................................30

2 of 180

Fulltext Search..............................................................................................................................................................31Using the Native Fulltext Search...............................................................................................................................31Using the Lucene Fulltext Search..............................................................................................................................31

User-Defined Variables..................................................................................................................................................32Date and Time..............................................................................................................................................................33Using Spring.................................................................................................................................................................33

Using the TCP Server...............................................................................................................................................33Error Code Incompatibility........................................................................................................................................33

OSGi............................................................................................................................................................................34Java Management Extension (JMX)................................................................................................................................34Feature List..................................................................................................................................................................35

Main Features..........................................................................................................................................................35Additional Features..................................................................................................................................................35SQL Support............................................................................................................................................................35Security Features.....................................................................................................................................................36Other Features and Tools.........................................................................................................................................36

Comparison to Other Database Engines..........................................................................................................................36DaffodilDb and One$Db............................................................................................................................................37McKoi......................................................................................................................................................................37

H2 in Use.....................................................................................................................................................................37Connection Modes.........................................................................................................................................................37

Embedded Mode......................................................................................................................................................37Server Mode............................................................................................................................................................38Mixed Mode.............................................................................................................................................................38

Database URL Overview................................................................................................................................................39Connecting to an Embedded (Local) Database................................................................................................................40In-Memory Databases...................................................................................................................................................40Database Files Encryption..............................................................................................................................................40

Creating a New Database with File Encryption...........................................................................................................40Connecting to an Encrypted Database.......................................................................................................................40Encrypting or Decrypting a Database........................................................................................................................41

Database File Locking....................................................................................................................................................41Opening a Database Only if it Already Exists...................................................................................................................41Closing a Database........................................................................................................................................................41

Delayed Database Closing........................................................................................................................................41Don't Close a Database when the VM Exits................................................................................................................42

Execute SQL on Connection...........................................................................................................................................42Ignore Unknown Settings..............................................................................................................................................42Changing Other Settings when Opening a Connection.....................................................................................................42Custom File Access Mode...............................................................................................................................................42Multiple Connections.....................................................................................................................................................43

Opening Multiple Databases at the Same Time..........................................................................................................43Multiple Connections to the Same Database: Client/Server.........................................................................................43Multithreading Support.............................................................................................................................................43Locking, Lock-Timeout, Deadlocks............................................................................................................................43Avoiding Deadlocks..................................................................................................................................................44

Database File Layout.....................................................................................................................................................44Moving and Renaming Database Files.......................................................................................................................44Backup....................................................................................................................................................................44

Logging and Recovery...................................................................................................................................................44Compatibility.................................................................................................................................................................44

Compatibility Modes.................................................................................................................................................45DB2 Compatibility Mode...........................................................................................................................................45Derby Compatibility Mode.........................................................................................................................................45HSQLDB Compatibility Mode.....................................................................................................................................45MS SQL Server Compatibility Mode............................................................................................................................45MySQL Compatibility Mode.......................................................................................................................................45Oracle Compatibility Mode........................................................................................................................................46PostgreSQL Compatibility Mode................................................................................................................................46

Auto-Reconnect............................................................................................................................................................46Automatic Mixed Mode..................................................................................................................................................46Page Size......................................................................................................................................................................47Using the Trace Options................................................................................................................................................47

Trace Options..........................................................................................................................................................47Setting the Maximum Size of the Trace File...............................................................................................................47Java Code Generation..............................................................................................................................................48

Using Other Logging APIs..............................................................................................................................................48Read Only Databases....................................................................................................................................................48Read Only Databases in Zip or Jar File............................................................................................................................48

Opening a Corrupted Database.................................................................................................................................49Computed Columns / Function Based Index....................................................................................................................49

3 of 180

Multi-Dimensional Indexes.............................................................................................................................................49User-Defined Functions and Stored Procedures...............................................................................................................50

Referencing a Compiled Method................................................................................................................................50Declaring Functions as Source Code..........................................................................................................................50Method Overloading.................................................................................................................................................51Function Data Type Mapping....................................................................................................................................51Functions That Require a Connection........................................................................................................................51Functions Throwing an Exception..............................................................................................................................51Functions Returning a Result Set..............................................................................................................................51Using SimpleResultSet..............................................................................................................................................51Using a Function as a Table......................................................................................................................................52

Pluggable or User-Defined Tables...................................................................................................................................52Triggers........................................................................................................................................................................53Compacting a Database.................................................................................................................................................53Cache Settings..............................................................................................................................................................54Performance Comparison...............................................................................................................................................55

Embedded...............................................................................................................................................................55Client-Server...........................................................................................................................................................55Benchmark Results and Comments...........................................................................................................................56

H2.....................................................................................................................................................................56HSQLDB.............................................................................................................................................................56Derby................................................................................................................................................................56PostgreSQL........................................................................................................................................................56MySQL...............................................................................................................................................................56Firebird..............................................................................................................................................................56Why Oracle / MS SQL Server / DB2 are Not Listed................................................................................................57

About this Benchmark..............................................................................................................................................57How to Run........................................................................................................................................................57Separate Process per Database...........................................................................................................................57Number of Connections.......................................................................................................................................57Real-World Tests................................................................................................................................................57Comparing Embedded with Server Databases.......................................................................................................57Test Platform.....................................................................................................................................................57Multiple Runs.....................................................................................................................................................57Memory Usage...................................................................................................................................................57Delayed Operations............................................................................................................................................58Transaction Commit / Durability..........................................................................................................................58Using Prepared Statements.................................................................................................................................58Currently Not Tested: Startup Time.....................................................................................................................58

PolePosition Benchmark.................................................................................................................................................58Database Performance Tuning.......................................................................................................................................59

Keep Connections Open or Use a Connection Pool.....................................................................................................59Use a Modern JVM...................................................................................................................................................59Virus Scanners.........................................................................................................................................................59Using the Trace Options...........................................................................................................................................59Index Usage............................................................................................................................................................59How Data is Stored Internally...................................................................................................................................59Optimizer................................................................................................................................................................60Expression Optimization...........................................................................................................................................60COUNT(*) Optimization............................................................................................................................................60Updating Optimizer Statistics / Column Selectivity......................................................................................................60In-Memory (Hash) Indexes.......................................................................................................................................60Use Prepared Statements.........................................................................................................................................61Prepared Statements and IN(...)...............................................................................................................................61Optimization Examples.............................................................................................................................................61Cache Size and Type................................................................................................................................................61Data Types..............................................................................................................................................................61Sorted Insert Optimization........................................................................................................................................61

Using the Built-In Profiler..............................................................................................................................................61Application Profiling.......................................................................................................................................................62

Analyze First............................................................................................................................................................62Database Profiling.........................................................................................................................................................62Statement Execution Plans............................................................................................................................................63

Displaying the Scan Count........................................................................................................................................63Special Optimizations...............................................................................................................................................64

How Data is Stored and How Indexes Work....................................................................................................................64Indexes...................................................................................................................................................................64Using Multiple Indexes.............................................................................................................................................65

Fast Database Import....................................................................................................................................................66Result Sets...................................................................................................................................................................67

Statements that Return a Result Set.........................................................................................................................674 of 180

Limiting the Number of Rows....................................................................................................................................67Large Result Sets and External Sorting......................................................................................................................67

Large Objects...............................................................................................................................................................68Storing and Reading Large Objects...........................................................................................................................68When to use CLOB/BLOB..........................................................................................................................................68Large Object Compression........................................................................................................................................68

Linked Tables...............................................................................................................................................................68Updatable Views...........................................................................................................................................................68Transaction Isolation.....................................................................................................................................................69

Table Level Locking..................................................................................................................................................69Lock Timeout...........................................................................................................................................................69

Multi-Version Concurrency Control (MVCC).....................................................................................................................70Clustering / High Availability..........................................................................................................................................70

Using the CreateCluster Tool....................................................................................................................................70Detect Which Cluster Instances are Running..............................................................................................................71Clustering Algorithm and Limitations.........................................................................................................................71

Two Phase Commit.......................................................................................................................................................71Compatibility.................................................................................................................................................................72

Transaction Commit when Autocommit is On.............................................................................................................72Keywords / Reserved Words.....................................................................................................................................72

Standards Compliance...................................................................................................................................................72Supported Character Sets, Character Encoding, and Unicode......................................................................................72

Run as Windows Service................................................................................................................................................72Install the Service....................................................................................................................................................72Start the Service......................................................................................................................................................73Connect to the H2 Console.......................................................................................................................................73Stop the Service......................................................................................................................................................73Uninstall the Service................................................................................................................................................73Additional JDBC drivers............................................................................................................................................73

ODBC Driver.................................................................................................................................................................73ODBC Installation....................................................................................................................................................73Starting the Server...................................................................................................................................................73ODBC Configuration.................................................................................................................................................74PG Protocol Support Limitations................................................................................................................................74Security Considerations............................................................................................................................................74Using Microsoft Access.............................................................................................................................................75

Using H2 in Microsoft .NET............................................................................................................................................75Using the ADO.NET API on .NET...............................................................................................................................75Using the JDBC API on .NET.....................................................................................................................................75

ACID............................................................................................................................................................................75Atomicity.................................................................................................................................................................75Consistency.............................................................................................................................................................76Isolation..................................................................................................................................................................76Durability................................................................................................................................................................76

Durability Problems.......................................................................................................................................................76Ways to (Not) Achieve Durability..............................................................................................................................76Running the Durability Test......................................................................................................................................77

Using the Recover Tool..................................................................................................................................................77File Locking Protocols....................................................................................................................................................77

File Locking Method 'File'..........................................................................................................................................77File Locking Method 'Socket'.....................................................................................................................................78File Locking Method 'FS'...........................................................................................................................................78

Using Passwords...........................................................................................................................................................78Using Secure Passwords...........................................................................................................................................78Passwords: Using Char Arrays instead of Strings........................................................................................................79Passing the User Name and/or Password in the URL..................................................................................................79

Password Hash.............................................................................................................................................................79Protection against SQL Injection....................................................................................................................................80

What is SQL Injection...............................................................................................................................................80Disabling Literals......................................................................................................................................................80Using Constants.......................................................................................................................................................80Using the ZERO() Function.......................................................................................................................................81

Protection against Remote Access..................................................................................................................................81Restricting Class Loading and Usage..............................................................................................................................81Security Protocols.........................................................................................................................................................81

User Password Encryption........................................................................................................................................81File Encryption.........................................................................................................................................................82Wrong Password / User Name Delay.........................................................................................................................82HTTPS Connections..................................................................................................................................................82

SSL/TLS Connections.....................................................................................................................................................82Universally Unique Identifiers (UUID).............................................................................................................................83

5 of 180

Recursive Queries.........................................................................................................................................................83Settings Read from System Properties............................................................................................................................84Setting the Server Bind Address.....................................................................................................................................84Pluggable File System....................................................................................................................................................84Split File System...........................................................................................................................................................85Database Upgrade........................................................................................................................................................85Java Objects Serialization..............................................................................................................................................85Limits and Limitations....................................................................................................................................................86Glossary and Links........................................................................................................................................................86Index...........................................................................................................................................................................87

Commands (Data Manipulation)................................................................................................................................87Commands (Data Definition).....................................................................................................................................87Commands (Other)..................................................................................................................................................88Other Grammar.......................................................................................................................................................88System Tables.........................................................................................................................................................89SELECT...................................................................................................................................................................89INSERT...................................................................................................................................................................90UPDATE..................................................................................................................................................................90DELETE...................................................................................................................................................................90BACKUP..................................................................................................................................................................90CALL.......................................................................................................................................................................91EXPLAIN..................................................................................................................................................................91MERGE....................................................................................................................................................................91RUNSCRIPT.............................................................................................................................................................91SCRIPT...................................................................................................................................................................92SHOW.....................................................................................................................................................................92ALTER INDEX RENAME.............................................................................................................................................92ALTER SCHEMA RENAME..........................................................................................................................................93ALTER SEQUENCE....................................................................................................................................................93ALTER TABLE ADD...................................................................................................................................................93ALTER TABLE ADD CONSTRAINT..............................................................................................................................93ALTER TABLE ALTER COLUMN..................................................................................................................................93ALTER TABLE DROP COLUMN...................................................................................................................................94ALTER TABLE DROP CONSTRAINT............................................................................................................................94ALTER TABLE SET....................................................................................................................................................95ALTER TABLE RENAME.............................................................................................................................................95ALTER USER ADMIN................................................................................................................................................95ALTER USER RENAME..............................................................................................................................................95ALTER USER SET PASSWORD...................................................................................................................................95ALTER VIEW............................................................................................................................................................96ANALYZE.................................................................................................................................................................96COMMENT...............................................................................................................................................................96CREATE AGGREGATE...............................................................................................................................................96CREATE ALIAS.........................................................................................................................................................97CREATE CONSTANT.................................................................................................................................................97CREATE DOMAIN.....................................................................................................................................................98CREATE INDEX........................................................................................................................................................98CREATE LINKED TABLE............................................................................................................................................98CREATE ROLE..........................................................................................................................................................99CREATE SCHEMA.....................................................................................................................................................99CREATE SEQUENCE.................................................................................................................................................99CREATE TABLE......................................................................................................................................................100CREATE TRIGGER..................................................................................................................................................100CREATE USER........................................................................................................................................................101CREATE VIEW........................................................................................................................................................101DROP AGGREGATE.................................................................................................................................................101DROP ALIAS..........................................................................................................................................................102DROP ALL OBJECTS...............................................................................................................................................102DROP CONSTANT..................................................................................................................................................102DROP DOMAIN......................................................................................................................................................102DROP INDEX.........................................................................................................................................................102DROP ROLE...........................................................................................................................................................103DROP SCHEMA......................................................................................................................................................103DROP SEQUENCE...................................................................................................................................................103DROP TABLE..........................................................................................................................................................103DROP TRIGGER.....................................................................................................................................................103DROP USER...........................................................................................................................................................104DROP VIEW...........................................................................................................................................................104TRUNCATE TABLE..................................................................................................................................................104CHECKPOINT.........................................................................................................................................................104CHECKPOINT SYNC................................................................................................................................................105

6 of 180

COMMIT................................................................................................................................................................105COMMIT TRANSACTION.........................................................................................................................................105GRANT RIGHT.......................................................................................................................................................105GRANT ALTER ANY SCHEMA...................................................................................................................................105GRANT ROLE.........................................................................................................................................................106HELP.....................................................................................................................................................................106PREPARE COMMIT.................................................................................................................................................106REVOKE RIGHT......................................................................................................................................................106REVOKE ROLE.......................................................................................................................................................106ROLLBACK.............................................................................................................................................................107ROLLBACK TRANSACTION......................................................................................................................................107SAVEPOINT...........................................................................................................................................................107SET @...................................................................................................................................................................107SET ALLOW_LITERALS...........................................................................................................................................108SET AUTOCOMMIT.................................................................................................................................................108SET CACHE_SIZE...................................................................................................................................................108SET CLUSTER........................................................................................................................................................108SET BINARY_COLLATION.......................................................................................................................................109SET COLLATION....................................................................................................................................................109SET COMPRESS_LOB..............................................................................................................................................109SET DATABASE_EVENT_LISTENER..........................................................................................................................110SET DB_CLOSE_DELAY...........................................................................................................................................110SET DEFAULT_LOCK_TIMEOUT..............................................................................................................................110SET DEFAULT_TABLE_TYPE....................................................................................................................................110SET EXCLUSIVE.....................................................................................................................................................111SET IGNORECASE..................................................................................................................................................111SET JAVA_OBJECT_SERIALIZER..............................................................................................................................111SET LOG................................................................................................................................................................111SET LOCK_MODE...................................................................................................................................................112SET LOCK_TIMEOUT..............................................................................................................................................112SET MAX_LENGTH_INPLACE_LOB...........................................................................................................................113SET MAX_LOG_SIZE...............................................................................................................................................113SET MAX_MEMORY_ROWS.....................................................................................................................................113SET MAX_MEMORY_UNDO.....................................................................................................................................113SET MAX_OPERATION_MEMORY............................................................................................................................114SET MODE.............................................................................................................................................................114SET MULTI_THREADED..........................................................................................................................................114SET OPTIMIZE_REUSE_RESULTS............................................................................................................................114SET PASSWORD.....................................................................................................................................................115SET QUERY_STATISTICS........................................................................................................................................115SET QUERY_TIMEOUT...........................................................................................................................................115SET REFERENTIAL_INTEGRITY...............................................................................................................................115SET RETENTION_TIME...........................................................................................................................................116SET SALT HASH.....................................................................................................................................................116SET SCHEMA.........................................................................................................................................................116SET SCHEMA_SEARCH_PATH..................................................................................................................................116SET THROTTLE......................................................................................................................................................117SET TRACE_LEVEL.................................................................................................................................................117SET TRACE_MAX_FILE_SIZE...................................................................................................................................117SET UNDO_LOG.....................................................................................................................................................117SET WRITE_DELAY................................................................................................................................................118SHUTDOWN..........................................................................................................................................................118Alias......................................................................................................................................................................118And Condition........................................................................................................................................................118Array.....................................................................................................................................................................119Boolean.................................................................................................................................................................119Bytes.....................................................................................................................................................................119Case......................................................................................................................................................................119Case When............................................................................................................................................................119Cipher...................................................................................................................................................................120Column Definition..................................................................................................................................................120Comments.............................................................................................................................................................120Compare...............................................................................................................................................................120Condition...............................................................................................................................................................121Condition Right Hand Side......................................................................................................................................121Constraint..............................................................................................................................................................121Constraint Name Definition.....................................................................................................................................122Csv Options...........................................................................................................................................................122Data Type.............................................................................................................................................................122Date......................................................................................................................................................................123Decimal.................................................................................................................................................................123

7 of 180

Digit......................................................................................................................................................................123Dollar Quoted String...............................................................................................................................................123Expression.............................................................................................................................................................123Factor...................................................................................................................................................................124Hex.......................................................................................................................................................................124Hex Number..........................................................................................................................................................124Index Column........................................................................................................................................................124Int........................................................................................................................................................................124Long.....................................................................................................................................................................125Name....................................................................................................................................................................125Null.......................................................................................................................................................................125Number.................................................................................................................................................................125Numeric................................................................................................................................................................125Operand................................................................................................................................................................126Order....................................................................................................................................................................126Quoted Name........................................................................................................................................................126Referential Constraint.............................................................................................................................................126Referential Action...................................................................................................................................................126Script Compression Encryption................................................................................................................................127Select Expression...................................................................................................................................................127String....................................................................................................................................................................127Summand..............................................................................................................................................................127Table Expression....................................................................................................................................................128Values Expression..................................................................................................................................................128Term.....................................................................................................................................................................128Time.....................................................................................................................................................................128Timestamp............................................................................................................................................................129Value....................................................................................................................................................................129Information Schema...............................................................................................................................................129Range Table..........................................................................................................................................................130

Index.........................................................................................................................................................................131Aggregate Functions..............................................................................................................................................131Numeric Functions.................................................................................................................................................131String Functions.....................................................................................................................................................132Time and Date Functions........................................................................................................................................132System Functions...................................................................................................................................................133AVG......................................................................................................................................................................133BOOL_AND............................................................................................................................................................133BOOL_OR..............................................................................................................................................................134COUNT..................................................................................................................................................................134GROUP_CONCAT....................................................................................................................................................134MAX......................................................................................................................................................................134MIN.......................................................................................................................................................................134SUM......................................................................................................................................................................135SELECTIVITY.........................................................................................................................................................135STDDEV_POP.........................................................................................................................................................135STDDEV_SAMP......................................................................................................................................................135VAR_POP...............................................................................................................................................................135VAR_SAMP............................................................................................................................................................136ABS.......................................................................................................................................................................136ACOS....................................................................................................................................................................136ASIN.....................................................................................................................................................................136ATAN....................................................................................................................................................................136COS......................................................................................................................................................................137COSH....................................................................................................................................................................137COT......................................................................................................................................................................137SIN.......................................................................................................................................................................137SINH.....................................................................................................................................................................137TAN......................................................................................................................................................................138TANH....................................................................................................................................................................138ATAN2...................................................................................................................................................................138BITAND.................................................................................................................................................................138BITOR...................................................................................................................................................................138BITXOR.................................................................................................................................................................139MOD.....................................................................................................................................................................139CEILING................................................................................................................................................................139DEGREES...............................................................................................................................................................139EXP.......................................................................................................................................................................139FLOOR..................................................................................................................................................................139LOG......................................................................................................................................................................140LOG10...................................................................................................................................................................140

8 of 180

RADIANS...............................................................................................................................................................140SQRT....................................................................................................................................................................140PI.........................................................................................................................................................................140POWER.................................................................................................................................................................141RAND....................................................................................................................................................................141RANDOM_UUID.....................................................................................................................................................141ROUND.................................................................................................................................................................141ROUNDMAGIC.......................................................................................................................................................141SECURE_RAND......................................................................................................................................................142SIGN.....................................................................................................................................................................142ENCRYPT...............................................................................................................................................................142DECRYPT...............................................................................................................................................................142HASH....................................................................................................................................................................142TRUNCATE............................................................................................................................................................143COMPRESS............................................................................................................................................................143EXPAND................................................................................................................................................................143ZERO....................................................................................................................................................................143ASCII....................................................................................................................................................................143BIT_LENGTH.........................................................................................................................................................144LENGTH................................................................................................................................................................144OCTET_LENGTH....................................................................................................................................................144CHAR....................................................................................................................................................................144CONCAT................................................................................................................................................................144CONCAT_WS.........................................................................................................................................................145DIFFERENCE..........................................................................................................................................................145HEXTORAW...........................................................................................................................................................145RAWTOHEX...........................................................................................................................................................145INSTR...................................................................................................................................................................145INSERT Function....................................................................................................................................................146LOWER..................................................................................................................................................................146UPPER...................................................................................................................................................................146LEFT.....................................................................................................................................................................146RIGHT...................................................................................................................................................................146LOCATE.................................................................................................................................................................147POSITION.............................................................................................................................................................147LPAD.....................................................................................................................................................................147RPAD....................................................................................................................................................................147LTRIM...................................................................................................................................................................147RTRIM...................................................................................................................................................................148TRIM.....................................................................................................................................................................148REGEXP_REPLACE..................................................................................................................................................148REPEAT.................................................................................................................................................................148REPLACE...............................................................................................................................................................148SOUNDEX..............................................................................................................................................................149SPACE...................................................................................................................................................................149STRINGDECODE....................................................................................................................................................149STRINGENCODE........................................................