Search
Submit & Win
Submit and win
Cash Prizes !
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Sun, 21-Dec-2014 Authors
All Time Authors
Sourav.Kayal
39750
SheoNarayan
38550
Niladri.biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

Use wildcards characters in SQL queries

Samrat153
Posted by under Sql Server category on for Beginner level | Points: 200 | Views : 5633 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
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.


Page copy protected against web site content infringement by Copyscape
About the Author

Samrat153

Full Name: Samrat Banerjee
Member Level: Starter
Member Status: Member
Member Since: 2/12/2011 10:14:56 AM
Country: India

http://www.dotnetfunda.com

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.
Share this to:

Bookmark It

Comments or Responses

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

hi

useful article for newer to sql

nice explained

Thanks

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)