Complex JPQL String Query

Question

I am trying to execute a rather complicated query in a string field in a database. I am not very experienced in JPQL, so I thought I would try to get some help.

I have a field in the database FILE_PATH. The field FILE_PATHwill contain values ​​such as:

• "C: \ Temp \ Files \ filename.txt
• 'File: \\\ C: \ testing \ testfolder \ innerfolder \ filename2.txt

I need to be able to search from a user-specified query for a file name only. Thus, instead of just executing SELECT Table FROM Table AS t WHERE t.filePath LIKE '%:query%', it will be a little more complicated to accommodate only part of the path file. The file path and file name are dynamic data, so I cannot just copy the prefix line. This bothers me a lot, but I know that in JPQL there are some string expressions that could handle this requirement.

Basically, I just need to return all the lines corresponding to this query to everything that comes after the last "\" in the field FILE_PATH. Is it possible?

Thanks for the help.

EDIT: The database used is SQL Server.

Answer 1

WHERE:

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

. .

.

Answer 2

, , . , ( ):

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%'

SQL, , JPQL: http://www.datanucleus.org/products/accessplatform/jpa/jpql_functions.html

(, - 1). " ". H2, MySQL Apache Derby. Oracle, SQL Server ( ). "\" "\\" (MySQL, PostgreSQL; , Hibernate ).