Gonzalo Calvo Gonzalo Calvo - 8 days ago 6
SQL Question

(Hibernate) searching for strings that match a pattern

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.

Cheers.

Answer

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, ':'))
Comments