C# check string contain a word with underscores

Question

I want to get all items which contain "_AT_" or "PV_", but d.Nom.Contains($"PV_") and d.Nom.Contains($"_AT_") gets also items containing only "AT" and "PV"

 IQueryable<DocumentMetadata> docPV = NHibernateSession.Current.Query<DocumentMetadata>()
           .Where(d => d.IdEntiteRattachement == missionId
                       && d.Nom.Contains($"PV_")
                       && d.Desactive == false)
           .OrderByDescending(d => d.DateDerniereModif);

        IList<DocumentMetadata> docAR = NHibernateSession.Current.Query<DocumentMetadata>()
           .Where(d => d.IdEntiteRattachement == missionId
                       && d.Nom.Contains($"_AT_")
                       && d.Desactive == false)
           .OrderByDescending(d => d.DateDerniereModif).ToList();

Are you asking how to combine those conditions? As in `&& (d.Nom.Contains($"PV_") || d.Nom.Contains($"_AT_"))` — Lithium, Apr 10 '18 at 10:15
In SQL, underscore (and percent) are wild cards. You have to escape them. You most probably will find that in the docs. — Stefan Steinegger, Apr 10 '18 at 10:19
Is it because the underscore is acting as a wildcard? Does this help - https://stackoverflow.com/questions/5821/sql-server-escape-an-underscore? (i.e. d.Nom.Contains(@"PV\_") - Note you need '@' because \ is an escape char itself. — Adam Benson, Apr 10 '18 at 10:19
Don't close this question for being unclear. IMHO, it's an clear question: filtering for `Contains("_AT_")` also finds strings that only contain `"AT"`. (However it may be a duplicate.) — Stefan Steinegger, Apr 10 '18 at 10:26

score 2 · Accepted Answer · answered Apr 10 '18 at 10:22

2

In SQL, underscore (and percent) are wild cards. NHibernate doesn't automatically escape them, because you can make use of them. Behind .Contains, there is SQL's LIKE.

Escaping wildcards depend on the underlying DBMS.

Try this:

d.Nom.Contains("\\_AT\\_")

(It may not work. See the docs of your database engine.)

answered Apr 10 '18 at 10:22

Stefan Steinegger

63,782
15
129
193

or `.Contains(@"\_AT\_")` – Cleptus Apr 10 '18 at 10:23
I test those 2 codes, but they do not work. Then I use `Nom.Replace("_","|").Contains("PV|")` – AnnaCICI Apr 10 '18 at 11:48
Sounds like a working solution. However, it has two draw backs: You cannot distinguish between "PV_" and "PV|", and the database won't use indexes. When a database runs a `LIKE`, it usually cannot use indexes anyway. But there may be special optimizations e.g. when using `StartsWith` instead of `Contains`. It depends on the database. What database do you use? – Stefan Steinegger Apr 12 '18 at 07:41
For many common databases, it often CAN use indexes (for some of the work) for LIKE, assuming that the pattern contains a non-wildcard initial part. – Oskar Berggren Apr 16 '18 at 21:05
@OskarBerggren: Yes, that's why I say `StartsWith` instead of `Contains`. – Stefan Steinegger Apr 19 '18 at 11:18

score 0 · Answer 2 · answered Dec 20 '19 at 15:28

Somehow none of the solutions given above would escape the underscore properly (I tried \\_ @\_ [_] $_ ^_) so I ended up filtering again in pure LINQ:

var list = dc.Employees.Where(a => a.Name.Contains(partial)).ToList();
if (partial.Contains("_")) // underscore is an sql wildcard character and will match anything, so filter again in .NET linq
    list = list.Where(a => a.Name.Contains(partial)).ToList();

This is obviously not the best solution since it retrieves a bigger amount of rows from the DB than necessary and filters again in memory, but in my case the overhead is acceptable.

C# check string contain a word with underscores

2 Answers2