# How to do a database query on substrings?

With the package DatabaseLink I can open a connection to my MYSQL database. Then I can use SQL commands like

SQLExecute[conn, "SELECT * FROM table WHERE column ='some string'"]

SQLExecute[conn, "SELECT * FROM table WHERE column LIKE '%abcd%'"]


With the first command, we look for rows where the column value equals the given string, with the second one for rows where the column string contains the substring 'abcd'.

In Mathematica 12 we have the experimental functions DatabaseReference, RelationalDatabase and EntityStore. The documentation describes how we can use these functions to set up a connection with a database. That works fine. I can inspect all rows of the tables in my database as Entities, of which the types are the table names. So many of these Entities have strings as properties.

Now I want to do queries on the database. For example, return those Entities of type 'table' for which property 'column' equals 'some string'. That is the first SQL-command above. I found it can be done in the following way ( I am not sure if that is the best way for doing a query):

EntityList @ EntityClass["table", "column"->"some string"]


For the second SQL-example, I tried a lot, e.g.

EntityList @ EntityClass["table", "column"->(StringContainsQ[ #, "abcd"]&)]


This results in an error message that the StringContainsQ expression cannot be compiled to SQL.

Any hint how I can do a query on substrings of string values is highly welcome.

• I think you are looking for EntityFunction, but I'm a little unfamiliar with it right now. – Carl Lange May 9 at 19:42

I am one of the developers for the new relational database connectivity features.

Very soon, a rather extensive tutorial will be added to the current documentation (via documentation paclet update), which would contain, in particular, examples of the kind you are interested in.

What you need is:

EntityList @ FilteredEntityClass[
"table",
EntityFunction[row, StringContainsQ[row["column"], "abcd"]]
]


Think of EntityFunction as an analogue of Function with named argument, where the argument is bound to a row of a table (in this context).

B.t.w., EntityList may not always be what you want. If you want to extract some fields (columns) from the resulting (virtual) table, you can use EntityValue:

EntityValue[
FilteredEntityClass[
"table",
EntityFunction[row, StringContainsQ[row["column"], "abcd"]]
],
a-list-of-column-names-you-want-to-extract
]


]

• Hi Leonid. Glad to see you again and thanks for this very useful answer. I had not yet discovered FilteredEntityClass and EntityFunction. My first impression is that it gives a nice and simple way of querying a private database. – Fred Simons May 10 at 6:46
• Hi Fred, I am happy to help. I think we've got a pretty powerful query-building sublanguage with the new db functionality, but obviously I am biased. Once the tutorial is added to the docs, it should answer many questions. Thanks for the accept. – Leonid Shifrin May 10 at 12:10