Use wildcards characters in SQL queries

Posted by in Sql Server category on for Beginner level | Points: 200 | Views : 9700 red flag

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


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.


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



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.


Matches any character in 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'.

     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 :)


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.

     SELECT * FROM Users WHERE UserName LIKE '%!_%'  escape '!';
After executing above query, Ii gives only return those UserName which contains ‘_’ .


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

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Samrat Banerjee
Member Level: Starter
Member Status: Member
Member Since: 2/12/2011 10:14:56 AM
Country: India
Hi, I am Samrat Banerjee from India. I am a Software Engineer working in .net platform. I love to explore new technologies.

Login to vote for this post.

Comments or Responses

Posted by: Patel28rajendra on: 3/15/2012 | Points: 25

useful article for newer to sql

nice explained

Posted by: Yrbyogi on: 3/17/2012 | Points: 25
We can also use the PATINDEX sql function (using regex) to replace the unwanted characters.

Login to post response

Comment using Facebook(Author doesn't get notification)