converting string to number inside Jooq select - Oracle

Question

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 1

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?

Answer 2

substring(Field<String >, int, int) has an overloaded method substring(Field<String>, Field<? Extends Integer>, Field<? Extends Integer>)

of course the first method internally calling the second one.

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