HQL substring last x characters

Question

I am quite new to Hibernate, and I would like to get the last N characters of a string. I found the substring method, but that didn't really help :(

Does anyone have any idea?

TIA

Answer 1

I hit this problem not too long ago resolved it, hit it again and returned back to how I resolved it a few weeks back.

Assuming as an example you are uploading a file. The file will go to something like /tmp/user/1/image.jpg or c:\temp\user\1\image.jpg The upload process needs to be written in a way that the actual path for image storage is generated for it and can be regenerated so assuming our path is

String actualPath='/tmp'
String user=user+"/"
Long id=1
 String getPath() {
  return "${actualPath}/${user}/${id}"
 }
String generatePath(actualPath,user,id) {
  return "${actualPath}/${user}/${id}"
 }

When attempting to do our HQL we need to be able to extract what is not actual parth of path so $path or generatePath(actualPath,user,id) should return the same then hql would be something like outside of hql :

 int filePosition = filePosition(actualPath,user,id)

In HQL:

, substring(drd.image,${filePosition+1},LENGTH(drd.image)) as fileName) 

outside of current function:

  //where filePosition returns size of generic string bound to string image...
 private int filePosition(String actualPath,user,id) {
            return (actualPath+user+id).size()
 }

If image contained full image path including it's path the output of fileName in our substring above will be everything beyond actual path i.e. actual fileName uploaded.

Answer 2

I actually fixed it using substring. I forgot that I can use parameters. I did something like:

Query q = " .. where substring (field, :offset, :length) = something_else"
q.setParameter("offset", field.length() - N + 1);
q.setParameter("length", N);

Answer 3

I am surprised this didn't help substring(int beginIndex, int endIndex). But what about split(String regex, int limit). I think a little more information is needed of what you are trying to accomplish.