用字段提取行的HQL函数只包含数字(HQL function to extract rows with field contains only number)

用字段提取行的HQL函数只包含数字(HQL function to extract rows with field contains only number)

我有一个小问题要公开：

我想从实体中提取，假设其名称为“CustomerDetail”，其中特定字段（代码）只有数字字符的所有行。

在HQL中不存在像Sql Server这样的ISNUMERIC（）函数，以及应用regex函数的可能性。

一个可能的解决方案如下：

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

等等重复这个条件整体alfabetical字母和特殊字符。

我认为这是一个糟糕的解决方案，性能水平低（LIKE数量巨大）

请，你能给我一个更聪明的解决方案吗？

先谢谢你

PS我的应用程序是多DBMS，所以我不能使用SQL查询

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

最满意答案

主要编辑：我的错误，我误以为正确的工作函数isNumeric（）为一个HQL函数是错误的。 根据文档 HQL支持数据库标量函数 - 而SQLServer（我测试过）有一个isNumeric（）函数。

我现在看到两个选项：

选项1：您可以为不同的数据库编写不同的HQL，这些数据库利用SQLServer中的isNumeric（）等函数构建函数（但是，在“遍地写入一次”中，这是一大步。

选项2：为每个想要符合SQL自定义函数的数据库编写代码，并在您的方言中使用相同的名称进行注册。

我知道，这两个选项都不是很了不起，但我想不出任何其他方式来实现它。

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.