Use wildcards characters in SQL queries

Use wildcards characters like %, _ in the where clause of an sql statement.
Manage Error "xml parsing <line and character details> illegal name character"

Introduction

Most SQL users know the importance and power of the basic usage of wildcards. Using wildcards allows us to do pattern matches. And the most common example of this is The LIKE condition which can be used in any valid SQL statement. But Here I am not discussing about it.

Objective

Here I am discuss about condition when user want to use wildcards in the where clause of an SQL statement.

Wildcard characters List

wildcard characters	Description
%	The % wildcard matches zero or more characters of any type.    Matches any character or multiple characters in its position.
_	The _ wildcard matches exactly one character of any type.   Matches any single character in its position.
[list]	Matches any character in list.
[^list]	Excludes any character in list.

 

Use wildcard characters in query

Suppose I want all users whose name contains _. For example, it would be a value such as 'sam_b'.
Ex.

     SELECT * FROM Users WHERE UserName LIKE '_';

What kind of output you can predict after execute above query. Is It will gives UserName contains ‘_’ ? No it will return all the records.

Another important point is which I would to share here that if you are using Stored Procedures & proper sqlParameters then anything mentioned in wildcard character inside XML tags will throw below mentioned exception.
Error: xml parsing <line and character details> illegal name character

Now you must be thinking about what is the solution ???
Here it is :)

Solution

Use the ESCAPE clause!

This will return all records from the wildcard field or we can say that by using this clause we're explicitly telling SQL that what escape need to escape. SQL will be treated them like a part of the string not like any special wildcard character.

Ex.

     SELECT * FROM Users WHERE UserName LIKE '%!_%'  escape '!';

After executing above query, Ii gives only return those UserName which contains ‘_’ .

Conclusion

In this article I have shown you a basics use of wildcards in the where clause of an SQL statement.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.