How to create variable pattern matching for database lookup?

Question

I'd like to find a table row where the desired value has the form of A9-B19-C12-D1. Thus, variable letters followed by variable digits, devided by - each. The length is not fixed, eg it could also be only A9 or A9-D1.

I only care about the letters. Now I have the 4 letters A, B, C, D and I want to run a query against postgres db that there is an entry in the format above.

Would I do this with the following statement?

SELECT * FROM mytable t WHERE t.entry LIKE 'A%B%C%D%';

If this is correct: how could I formulate a NamedQuery where I could provide the letters as variable parameters?

Answer 1

Try using SIMILAR TO with a reqular expression A\d+(-B\d+(-C\d+(-D\d+)?)?)?

@NamedNativeQueries({
    @NamedNativeQuery(
    name = "finDesiredData",
    query = "select * from mytable t where t.entry SIMILAR TO :ent",
        resultClass = MyTable.class
    )
})

I would personally use java StringBuffer to concatenate letters into a correct regexp.