I can't find the hql to solve my problem.
I have 2 nice tables.
The first table has a column with strings in the form 'xxx-xxx-xxx'.
The second table has a column with strings in the form 'some_prefix:xxx-xxx-xxx'.
What I want to do is given a subset of rows in the second table, find all the entries in the first table that match in the 'xxx-xxx-xxx' part. And I know for sure there cannot be more that one entry in the fist table for each row in the second.
I'm looking for a hql query that fetches those objects but I could use a sql too.
you can use a combination of locate and substring function on column of the second table to get the string after the : sign.
I haven't tested it but it should be something like:
where table1.column = substring(table2.column, locate(table2.column, ':'))