Le 09/04/2014 11:29, Noel Grandin a écrit :
> Hi
>
> Thanks for the patch. Your work looks fine, please can I get a license
> statement?
>
> http://h2database.com/html/build.html#providing_patches
Hi,
Here is another version of this patch including user documentation, grammar
changes and changelog.
I wrote the code, it's mine, and I'm contributing it to H2 for distribution
multiple-licensed under the H2 License, version 1.0, and under the Eclipse
Public License, version 1.0 (http://h2database.com/html/license.html).
Regards.
--
Éric Chatellier - www.codelutin.com - 02.40.50.29.28
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
Index: src/docsrc/help/help.csv
===================================================================
--- src/docsrc/help/help.csv (revision 5595)
+++ src/docsrc/help/help.csv (working copy)
@@ -3361,6 +3361,14 @@
CALL TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY')
"
+"Functions (String)","TRANSLATE","
+TRANSLATE(value , searchString, replacementString]])
+","
+Oracle-compatible TRANSLATE function that replaces a sequence of characters in a string with another set of characters.
+","
+CALL TRANSLATE('Hello world', 'eo', 'EO')
+"
+
"Functions (Time and Date)","CURRENT_DATE","
{ CURRENT_DATE [ () ] | CURDATE() | SYSDATE | TODAY }
","
Index: src/docsrc/html/changelog.html
===================================================================
--- src/docsrc/html/changelog.html (revision 5595)
+++ src/docsrc/html/changelog.html (working copy)
@@ -46,6 +46,7 @@
<h2>Next Version (unreleased)</h2>
<ul><li>Support the data type "DATETIME2" as an alias for "DATETIME", for MS SQL Server compatibility.
+</li><li>Add Oracle-compatible TRANSLATE function, patch by Eric Chatellier.
</li></ul>
<h2>Version 1.3.176 (2014-04-05)</h2>
Index: src/main/org/h2/expression/Function.java
===================================================================
--- src/main/org/h2/expression/Function.java (revision 5595)
+++ src/main/org/h2/expression/Function.java (working copy)
@@ -7,6 +7,7 @@
package org.h2.expression;
import static org.h2.util.ToChar.toChar;
+
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
@@ -94,7 +95,7 @@
STRINGDECODE = 80, STRINGTOUTF8 = 81, UTF8TOSTRING = 82,
XMLATTR = 83, XMLNODE = 84, XMLCOMMENT = 85, XMLCDATA = 86,
XMLSTARTDOC = 87, XMLTEXT = 88, REGEXP_REPLACE = 89, RPAD = 90,
- LPAD = 91, CONCAT_WS = 92, TO_CHAR = 93;
+ LPAD = 91, CONCAT_WS = 92, TO_CHAR = 93, TRANSLATE = 94;
public static final int CURDATE = 100, CURTIME = 101, DATE_ADD = 102,
DATE_DIFF = 103, DAY_NAME = 104, DAY_OF_MONTH = 105,
@@ -299,6 +300,7 @@
addFunction("RPAD", RPAD, VAR_ARGS, Value.STRING);
addFunction("LPAD", LPAD, VAR_ARGS, Value.STRING);
addFunction("TO_CHAR", TO_CHAR, VAR_ARGS, Value.STRING);
+ addFunction("TRANSLATE", TRANSLATE, 3, Value.STRING);
// date
addFunctionNotDeterministic("CURRENT_DATE", CURRENT_DATE,
@@ -1401,6 +1403,14 @@
database.getMode().treatEmptyStringsAsNull);
}
break;
+ case TRANSLATE: {
+ String matching = v1.getString();
+ String replacement = v2.getString();
+ result = ValueString.get(
+ translate(v0.getString(), matching, replacement),
+ database.getMode().treatEmptyStringsAsNull);
+ break;
+ }
case H2VERSION:
result = ValueString.get(Constants.getVersion(),
database.getMode().treatEmptyStringsAsNull);
@@ -1910,6 +1920,38 @@
return e;
}
+ // this is the org.apache.commons.lang3.StringUtils
+ // #replaceChars(String, String, String) implementation
+ private static String translate(String str, String searchChars, String replaceChars) {
+
+ if (str == null || str.length() == 0 || searchChars == null || searchChars.length() == 0) {
+ return str;
+ }
+ if (replaceChars == null) {
+ replaceChars = ""; // EMPTY
+ }
+ boolean modified = false;
+ int replaceCharsLength = replaceChars.length();
+ int strLength = str.length();
+ StringBuilder buf = new StringBuilder(strLength);
+ for (int i = 0; i < strLength; i++) {
+ char ch = str.charAt(i);
+ int index = searchChars.indexOf(ch);
+ if (index >= 0) {
+ modified = true;
+ if (index < replaceCharsLength) {
+ buf.append(replaceChars.charAt(index));
+ }
+ } else {
+ buf.append(ch);
+ }
+ }
+ if (modified) {
+ return buf.toString();
+ }
+ return str;
+ }
+
private static double roundmagic(double d) {
if ((d < 0.0000000000001) && (d > -0.0000000000001)) {
return 0.0;
Index: src/test/org/h2/test/db/TestFunctions.java
===================================================================
--- src/test/org/h2/test/db/TestFunctions.java (revision 5595)
+++ src/test/org/h2/test/db/TestFunctions.java (working copy)
@@ -92,6 +92,7 @@
testToCharFromDateTime();
testToCharFromNumber();
testToCharFromText();
+ testTranslate();
// TODO
// testCachingOfDeterministicFunctionAlias();
@@ -1162,6 +1163,47 @@
conn.close();
}
+ private void testTranslate() throws SQLException {
+ Connection conn = getConnection("functions");
+ Statement stat = conn.createStatement();
+
+ String createSQL = "CREATE TABLE testTranslate(id BIGINT, " +
+ "txt1 varchar);";
+ stat.execute(createSQL);
+ stat.execute("insert into testTranslate(id, txt1) " +
+ "values(1, 'test1')");
+ stat.execute("insert into testTranslate(id, txt1) " +
+ "values(2, null)");
+ stat.execute("insert into testTranslate(id, txt1) " +
+ "values(3, '')");
+ stat.execute("insert into testTranslate(id, txt1) " +
+ "values(4, 'caps')");
+
+ String query = "SELECT translate(txt1, 'p', 'r') " +
+ "FROM testTranslate order by id asc";
+ ResultSet rs = stat.executeQuery(query);
+ rs.next();
+ String actual = rs.getString(1);
+ assertEquals("test1", actual);
+ rs.next();
+ actual = rs.getString(1);
+ assertNull(actual);
+ rs.next();
+ actual = rs.getString(1);
+ assertEquals("", actual);
+ rs.next();
+ actual = rs.getString(1);
+ assertEquals("cars", actual);
+ rs.close();
+
+ rs = stat.executeQuery("select translate(null,null,null)");
+ rs.next();
+ assertNull(rs.getObject(1));
+
+ stat.execute("drop table testTranslate");
+ conn.close();
+ }
+
private void testToCharFromDateTime() throws SQLException {
deleteDb("functions");
Connection conn = getConnection("functions");
Index: src/tools/org/h2/build/doc/dictionary.txt
===================================================================
--- src/tools/org/h2/build/doc/dictionary.txt (revision 5595)
+++ src/tools/org/h2/build/doc/dictionary.txt (working copy)
@@ -753,6 +753,6 @@
overwrote though randomize readability datagram rsync mongodb divides crypto
predicted prediction wojtek hops jurczyk cbtree predict vast assumption upside
adjusted lastly sgtatham cleaning gillet prevented
-angus bernd macdonald eckenfels granting moreover exponential transferring
+angus bernd chatellier macdonald eckenfels granting moreover exponential transferring
dedup megabyte breadth traversal affine tucc jentsch yyyymmdd vertica graf
mutate
