error syntax error at or near ":" for split_part () of createNativeQuery in spring boot for postgresql

Question

I'm getting SQL exception while executing the query which contains

split_part() method as split_part(value::TEXT,':', 1).

String queryStr = " select split_part(value::TEXT,':', 1) from table";

Query query = entityManager.createNativeQuery(queryStr);
List results = query.getResultList();

ERROR 2020-02-10 14:54:37,926 [http-nio-7070-exec-1] 142 - ERROR: syntax error at or near ":"
  Position: 855 

Answer 1

Your obfuscation layer probably chokes on the :: operator. Use the cast() operator instead:

String queryStr = " select split_part(cast(value as text),':', 1) from table";

But why do you think you need the cast to begin with? If you are storing : characters that column, it is most probably a text (or varchar) column anyway and you don't need a cast at all.