Hi,

I'm just wondering if I can do the string function Left(Field, length) using HQL?

For example, the select statement in SQL
SELECT Left(Lastname,1), Count(*)
FROM MyTable
GROUP BY Left(Lastname,1)
ORDER BY Left(Lastname,1)

I tried to do it but received the following error message:
undefined alias or unknown mapping: Left [select new countobj(Left(obj.LastName, 1), count(obj.ID)) from MyObj obj group by Left(obj.LastName, 1) order by Left(obj.LastName, 1)]

Will I able to accomplish this using HQL?

Thanks in advance.

I'm not aware of any, but you can use IQuery/session.CreateSQLQuery() to use a raw SQL query to populate a mapped entity. I've never used it for substrings, but have used it for aggregate functions. Check chapter 13 of the NHibernate docs and see if that does it for you.

Hi!

Thanks for your response. I've got it working with SQLQuery

Thanks again!

You should also be able to implement this using query substitution.

Symon.

True that, to check out that approach start with:
See Nhibernate docs chapter 3 Section 3.5.4

Whichever work best for your team I say.