Sophisticated JPQL String Query

Question

I am trying to execute a pretty-sophisticated query on a string field in the database. I am not very experienced at JPQL, so I thought I would try to get some help.

I have a field in the database called FILE_PATH. Within the FILE_PATH field, there will be values such as:

• 'C:\temp\files\filename.txt'
• 'file:\\\C:\testing\testfolder\innerfolder\filename2.txt'

I need to be able to do a search from a user-given query on the file name only. So, instead of simply doing a SELECT Table FROM Table AS t WHERE t.filePath LIKE '%:query%', things will have to get a bit more complicated to accomodate for just the filename portion of the path. The file path and file name are dynamic data, so I can't just hard-code a prefix string in there. This has me pretty confused, but I know there are some string expressions in JPQL that might be able to handle this requirement.

Basically, I just need to return all rows that match the given query on whatever comes after the last '\' in the FILE_PATH field. Is this possible?

Thanks for the help.

EDIT: Database that is being used is SQL Server.

Answer 1

Probably the best solution is to add a separate column that contains just the file name. If you can't, then this might work (depending on the database you use):

drop table test;
create table test(name varchar(255));
insert into test values('C:\temp\name2\filename.txt');
insert into test values('file:\\\C:\\innerfolder\filename2.txt');
select * from test 
where substring(name, locate('\', name, -1)) like '%name2%'

This is pure SQL, but as far as I understand all the functions are supported within JPQL: http://www.datanucleus.org/products/accessplatform/jpa/jpql_functions.html

One problem is the locate(,,-1). It means 'start from the end of the string'. It works for the H2 database, but not MySQL and Apache Derby. It might work for Oracle, SQL Server (I didn't test it). For some databases may need to replace '\' with '\\' (MySQL, PostgreSQL; not sure if Hibernate does that for you).

Answer 2

Final WHERE Clause:

LOWER(SUBSTRING(fs.filePath, LENGTH(fs.filePath) - (LOCATE('\\', REVERSE(fs.filePath)) - 2), (LOCATE('\\', REVERSE(fs.filePath)) - 1))) LIKE '%:query%'

NOTE: For performance, you might want to save the location of the slash.

Thanks to Thomas Mueller for the assistance.