1

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
wenky
  • 471
  • 1
  • 6
  • 14

2 Answers2

0

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?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • when we use either DSL.substring("hello123",6,2).cast(Integer.class) or DSL.substring("hello123",6,2).cast(SQLDataType. NUMERIC) both are yielding to Field> type and from Field<> to Integer we don't have any handy methods – wenky Jun 16 '22 at 12:27
  • I'm not sure I understand. A `Field` is an expression (in SQL) of type `T`. You can only get a `T` reference by executing the query and projecting the `Field` expression. Is that what your question is about? – Lukas Eder Jun 16 '22 at 12:51
0

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)
wenky
  • 471
  • 1
  • 6
  • 14