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.