converting string to number inside Jooq select – Oracle

I am using jooq’s DSL.select in my Java code, I have scenario where I need to determine the index from a string. Used DSL.substring("hello123",6,1) to get the desired index value as string, but no method supporting in DSL to convert it to a number. Example:

DSL.select(
       COL1, 
       COL2, 
       COL3,
       DSL.substring("Test123456"), 
       1, 
       DSL.substring("hello123",6,1))
   .from(TABLE)

the nested substring need to be converted into number

Answer

Converting strings to numbers

Use the CAST expression:

// Both are equivalent
cast(substring("hello123", 6), SQLDataType.NUMERIC);
substring("hello123", 6).cast(SQLDataType.NUMERIC);

As always, this is assuming the following static import:

import static org.jooq.impl.DSL.*;

Mixing Field<?> expressions with bind values

You’ve mixed your Field<?> expression with a bind value 1 in your SELECT clause. There’s no such convenience overload for SELECT (there would be too many permutations), so you’ll have to wrap the bind value explicitly using:

val(1)

See also: How do I create a Field<T> from a value T in jOOQ, explicitly?