Hello,
I am trying to select a subset of records where a text field has a well-defined format, an example may be something like [day of month]/[month of year]/[year]-[number]{1}/[number]{4}. In a usual t-sql query one might do a select like this.
SELECT x WHERE x.y LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]-[0-9]/[0-9][0-9][0-9][0-9]';
Initially I assumed it would translate to an x++ query like the following.
select x where x.y like "[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]-[0-9]/[0-9][0-9][0-9][0-9]";
But even in its most simplified form select x where x.y like '[0-9]%'; such a query does not work. Is there a way to use the t-sql syntax for like?
X++ doesn’t support regular expressions in queries.
Let me quote F&O documentation:
The like operator can use * as a wildcard character for zero or more characters, and ? as a wildcard character for one character.
That is unfortunate, does this page of the docs contain all the more advanced querying options?
It seems kind of limiting and doing the extra subselection in dynamics unfortunately causes significant overhead due to the overfetching. If that is the only option then it’ll work but it is far from ideal.
Thank you for the quick reply, I appreciate it a ton.
The page you mentioned is about the query framework, not select statements in X++. And no, it doesn’t have any more features. It’s “more advanced” in the meaning of what users can do, but it’s just a subset of what you can do in code.
I completely agree that the options for querying DB are limited and it often leads to inefficient solutions, but it’s what it is. In some cases, an option may be doing the job outside F&O, e.g. in Power BI. You can also use direct SQL, but one must be very careful with it.