text: /*
   * Copyright 2004-2013 H2 Group. Multiple-Licensed under the H2 License,
   * Version 1.0, and under the Eclipse Public License, Version 1.0
   * (http://h2database.com/html/license.html).
   * Initial Developer: H2 Group
   */
  
  -- TO_DATE
  create alias TO_DATE as $$
  java.util.Date toDate(String s) throws Exception {
      return new java.text.SimpleDateFormat("yyyy.MM.dd").parse(s);
  }
  $$;
  call TO_DATE('1990.02.03')
  
  -- TO_CHAR
  drop alias if exists TO_CHAR;
  create alias TO_CHAR as $$
  String toChar(BigDecimal x, String pattern) throws Exception {
      return new java.text.DecimalFormat(pattern).format(x);
  }
  $$;
  call TO_CHAR(123456789.12, '###,###,###,###.##');
  
  -- update all rows in all tables
  select 'update ' || table_schema || '.' || table_name || ' set ' || column_name || '=' || column_name || ';'
  from information_schema.columns where ORDINAL_POSITION = 1 and table_schema <> 'INFORMATION_SCHEMA';
  
  -- read the first few bytes from a BLOB
  drop table test;
  drop alias first_bytes;
  create alias first_bytes as $$
  import java.io.*;
  @CODE
  byte[] firstBytes(InputStream in, int len) throws IOException {
      try {
          byte[] data = new byte[len];
          DataInputStream dIn = new DataInputStream(in);
          dIn.readFully(data, 0, len);
          return data;
      } finally {
          in.close();
      }
  }
  $$;
  create table test(data blob);
  insert into test values('010203040506070809');
  select first_bytes(data, 3) from test;
  
  -- encrypt and decrypt strings
  CALL CAST(ENCRYPT('AES', HASH('SHA256', STRINGTOUTF8('key'), 1), STRINGTOUTF8('Hello')) AS VARCHAR);
  CALL TRIM(CHAR(0) FROM UTF8TOSTRING(DECRYPT('AES', HASH('SHA256', STRINGTOUTF8('key'), 1), '16e44604230717eec9f5fa6058e77e83')));
  DROP ALIAS ENC;
  DROP ALIAS DEC;
  CREATE ALIAS ENC AS $$
  import org.h2.security.*;
  import org.h2.util.*;
  @CODE
  String encrypt(String data, String key) throws Exception {
      byte[] k = new SHA256().getHash(key.getBytes("UTF-8"), false);
      byte[] b1 = data.getBytes("UTF-8");
      byte[] buff = new byte[(b1.length + 15) / 16 * 16];
      System.arraycopy(b1, 0, buff, 0, b1.length);
      BlockCipher bc = CipherFactory.getBlockCipher("AES");
      bc.setKey(k);
      bc.encrypt(buff, 0, buff.length);
      return ByteUtils.convertBytesToString(buff);
  }
  $$;
  CREATE ALIAS DEC AS $$
  import org.h2.security.*;
  import org.h2.util.*;
  @CODE
  String decrypt(String data, String key) throws Exception {
      byte[] k = new SHA256().getHash(key.getBytes("UTF-8"), false);
      byte[] buff = ByteUtils.convertStringToBytes(data);
      BlockCipher bc = CipherFactory.getBlockCipher("AES");
      bc.setKey(k);
      bc.decrypt(buff, 0, buff.length);
      return StringUtils.trim(new String(buff, "UTF-8"), false, true, "\u0000");
  }
  $$;
  CALL ENC('Hello', 'key');
  CALL DEC(ENC('Hello', 'key'), 'key');
  