Use wildcards characters in SQL queries
Posted by Samrat Banerjee
under Sql Server
on 3/14/2012 for Beginner level | Points: 200
| Views : 4526
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"
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
The % wildcard matches zero or more characters of any
Matches any character or
multiple characters in its position.
The _ wildcard matches exactly one character of any
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.
Found interesting? Add this to: