Today I was working on filtering a database table's results to a specific type of data on a project and learned something fun. I was using raw SQL queries to get a view of the data before I put it into entity framework and I was getting a weird result set. I knew the data had an underscore as a separator for the data I needed, so my filter looked kind of like this:
SELECT * FROM Table WHERE Type LIKE '%Type_%'
I expected to find any result with text on either side of the string "Type_", but my result set kept returning values with spaces and not just the one's with underscores between words. Using Entity Framework, my result set was actually coming in as I expected which didn't make any sense. So, a little research on SQL reserverd characters and it turns out "%" isn't the only wildcard in a SQL query; an underscore is a wildcard for a single character! Since I was looking for the literal underscore in my string, my query needed to look like this:
SELECT * FROM Table WHERE Type LIKE '%Type[_]%'
And now I get my expected results! Of course, Entity Framework knew that already, so when I watched the queries in sql browser, it had properly escaped the underscore. Now I know for next time I am writing sprocs or test queries.