NHibernate Query that simulates SQL Replace Function

Question

I have a phone number field that can have lots of different characters such as "-" "." "(" ")", etc. I want to search for a phone number but I want to exclude specific characters. I'm not sure how to simulate this functionality with NHibernate Criteria or HQL.

Answer 1

I ended up using the Criteria query with a SQL Restriction. It was something like this

Session.CreateCriteria<EntityName>()
.Add(Restrictions.Sql(
"Replace('ColumnToSearch','PatternToFind', 'ReplaceWithValue')"))

The Sql Restriction lets you run the exact same sql statement as you give it.