5

I've have a little issue to expose:

I want to extract from an entity, suppose its name "CustomerDetail" all rows where a specific field (code) has only numeric characters.

In HQL doesn't exist ISNUMERIC() function like Sql Server, and the possibility to apply regex function.

A possible solution is the following:

SELECT C
FROM CustomerDetail C
WHERE C.code NOT LIKE '%A%'
AND C.code NOT LIKE '%B%'

and so on repeating this condition overall alfabetical letter and special characters.

I think this is a poor solution, with a low level of performance (enormous number of LIKE)

Please, can you advice me a more smart solution?

Thank you in advance

P.S. My application is multi DBMS so I can't use SQL query

Joe Taras
  • 15,166
  • 7
  • 42
  • 55

2 Answers2

1

Major edit: My bad, I mistook the just working function isNumeric() for a HQL function which is wrong. According to the doc HQL supports database scalar functions - and SQLServer (on which I tested) has an isNumeric() function.

I see two options now:

Option 1: You can write different HQLs for different databases which utilizes the build in functions like isNumeric() on SQLServer (but that's a huge step back in terms of "write once run everywhere")

Option 2: Write for every database you want to be compliant with a custom function in SQL and register it with the same name in your dialect.

I know, both options aren't terrific but I can't think of any other way to get it working.

Marius K.
  • 355
  • 2
  • 12
  • Dear, HQL has'n got isNumeric (as I've written in my question) – Joe Taras Oct 15 '13 at 12:44
  • Have you TRIED my solution (incl. the `= 1`)? Because, like I've written in my answer - it works... – Marius K. Oct 15 '13 at 12:52
  • Before I write a question... I try my code. This is chapter 14 of HQL: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html Show allowed functions. – Joe Taras Oct 15 '13 at 12:58
0

Solution1:

 (CAST( value_to_check AS DECIMAL) IS NOT NULL)

Solution2 (you can extend it to check even hexadecimal numbers):

 ((size(SPLIT(value_to_check, "0"))-1) +
 (size(SPLIT(value_to_check, "1"))-1) +
 (size(SPLIT(value_to_check, "2"))-1) +
 (size(SPLIT(value_to_check, "3"))-1) +
 (size(SPLIT(value_to_check, "4"))-1) +
 (size(SPLIT(value_to_check, "5"))-1) +
 (size(SPLIT(value_to_check, "6"))-1) +
 (size(SPLIT(value_to_check, "7"))-1) +
 (size(SPLIT(value_to_check, "8"))-1) +
 (size(SPLIT(value_to_check, "9"))-1) = LENGTH(value_to_check))
user2866868
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 30 '21 at 16:04