All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Development versions: 3.12 | Unsupported versions: 3.2 | 3.1 | 3.0 | 2.6

String formatting can be done efficiently in the database before returning results to your Java application. As discussed in the chapter about SQL dialects string functions (as any function type) are mostly emulated in your database, in case they are not natively supported.

This is a list of numeric functions supported by jOOQ's DSL:

  • ASCII: Get the ASCII code of a character.
  • BIT_LENGTH: Get the length of a string in bits.
  • CHAR_LENGTH: Get the length of a string in characters.
  • CONCAT: Concatenate several strings.
  • ESCAPE: Escape a string for use with the LIKE predicate.
  • LENGTH: Get the length of a string.
  • LOWER: Get a string in lower case letters.
  • LPAD: Pad a string on the left side.
  • LTRIM: Trim a string on the left side.
  • OCTET_LENGTH: Get the length of a string in octets.
  • POSITION: Find a string within another string.
  • REPEAT: Repeat a string a given number of times.
  • REPLACE: Replace a string within another string.
  • RPAD: Pad a string on the right side.
  • RTRIM: Trim a string on the right side.
  • SUBSTRING: Get a substring of a string.
  • TRIM: Trim a string on both sides.
  • UPPER: Get a string in upper case letters.

Please refer to the DSL Javadoc for more details.

Regular expressions, REGEXP, REGEXP_LIKE, etc.

Various databases have some means of searching through columns using regular expressions if the LIKE predicate does not provide sufficient pattern matching power. While there are many different functions and operators in the various databases, jOOQ settled for the SQL:2008 standard REGEX_LIKE operator. Being an operator (and not a function), you should use the corresponding method on org.jooq.Field:

create.selectFrom(BOOK).where(TITLE.likeRegex("^.*SQL.*$")).fetch();

Note that the SQL standard specifies that patterns should follow the XQuery standards. In the real world, the POSIX regular expression standard is the most used one, some use Java regular expressions, and only a few ones use Perl regular expressions. jOOQ does not make any assumptions about regular expression syntax. For cross-database compatibility, please read the relevant database manuals carefully, to learn about the appropriate syntax. Please refer to the DSL Javadoc for more details.

The jOOQ Logo