TRANSLATE

Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

The TRANSLATE() function translates a set of characters to another set of characters within a string, based on matching positions within the search and replacement string.

SELECT translate('1 * [2 + 3]', '[]', '()');

create.select(translate(val("1 * [2 + 3]"), "[]", "()")).fetch();

The result being

+-------------+
| translate   |
+-------------+
| 1 * (2 + 3) |
+-------------+

Dialect support

This example using jOOQ:

translate(val("1 * [2 + 3]"), "[]", "()")

Translates to the following dialect specific expressions:

-- AURORA_POSTGRES, BIGQUERY, COCKROACHDB, EXASOL, H2, HSQLDB, ORACLE, POSTGRES, SNOWFLAKE, SQLSERVER, VERTICA, YUGABYTEDB
translate('1 * [2 + 3]', '[]', '()')

-- DB2
translate('1 * [2 + 3]', '()', '[]')

-- TERADATA
otranslate('1 * [2 + 3]', '[]', '()')

-- ACCESS, ASE, AURORA_MYSQL, DERBY, FIREBIRD, HANA, INFORMIX, MARIADB, MEMSQL, MYSQL, REDSHIFT, SQLDATAWAREHOUSE, SQLITE, 
-- SYBASE
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.17, see #10141), or translate your own on our website

The jOOQ User Manual : SQL building : Column expressions : String functions : TRANSLATE

previous : next