Wildcards in SQL Server 2005

SQL wildcards are used for searching data's in a database, which were used with LIKE operator. The SQL wildcards substitute one or more character, whenever search requirements arise for data in a table of the database.

Wildcard	Description
%	For zero or more characters
-	For exactly one character
[ ]	match any one characters in the specified in the location
^ (or) !	Any single character not within the specified range

 

Examples for SQL Wildcards

Just assume here, we have a SQL Table named as 'EMPLOYEE' and it has the following data as below.

EMPLOYEE ID	FIRST NAME	LAST NAME	COUNTRY	GRADE	JIONED DATE
E1000	DEEPAK	GOTHARI	India	I	20- 08-1996
E1001	NIRMALA	RAJAN	India	II	13-01-2000
E1002	ALBERT	THOMAS	USA	I	24-09-2000
E1003	SYENDLA	ALVA	Canada	I	06-07-2006
E1004	JARLESS	OLA	UK	III	07-09-2008
E1005	HANSEN	DOVL	China	III	05-14-1998
E1006	MARRY	JAAN	USA	II	30-04-1995

 

 Usage of  %  Wildcard

If you want to select the persons living in a Country which starts with "In" from the "EMPLOYEE" table, you should use the following SELECT statement:

SELECT * FROM EMPLOYEE WHERE Country LIKE 'In%'

 

The result will display like as below.

EMPLOYEE ID	FIRST NAME	LAST NAME	COUNTRY	GRADE	JIONED DATE
E1000	DEEPAK	GOTHARI	India	I	20- 08-1996
E1001	NIRMALA	RAJAN	India	II	13-01-2000

 

Next can select the persons living in a Country which ends with "da" from the "EMPLOYEE" table, for this use the following SELECT statement:

SELECT * FROM EMPLOYEE WHERE Country LIKE '%da%'

Result will be as follows.

EMPLOYEE ID	FIRST NAME	LAST NAME	COUNTRY	GRADE	JIONED DATE
E1003	SYENDLA	ALVA	Canada	I	06-07-2006

 

Next you can select the persons living in a Country which ends with "a" from the "EMPLOYEE" table, for this use the following SELECT statement:

SELECT * FROM EMPLOYEE WHERE Country LIKE '%a%'

Result will display entaire table due to all country name ends with 'a'.

EMPLOYEE ID	FIRST NAME	LAST NAME	COUNTRY	GRADE	JIONED DATE
E1000	DEEPAK	GOTHARI	India	I	20- 08-1996
E1001	NIRMALA	RAJAN	India	II	13-01-2000
E1002	ALBERT	THOMAS	USA	I	24-09-2000
E1003	SYENDLA	ALVA	Canada	I	06-07-2006
E1004	JARLESS	OLA	UK	III	07-09-2008
E1005	HANSEN	DOVL	China	III	05-14-1998
E1006	MARRY	JAAN	USA	II	30-04-1995

 

Usage of  _  Wildcard

Now you want to select the persons with their FIRST NAME which starts with any character followed by "a" from the "EMPLOYEE" table, for this requirement you should use the following SELECT statement:

SELECT * FROM EMPLOYEE WHERE FIRSTNAME LIKE '_a%'

EMPLOYEE ID	FIRST NAME	LAST NAME	COUNTRY	GRADE	JIONED DATE
E1004	JARLESS	OLA	UK	III	07-09-2008
E1005	HANSEN	DOVL	China	III	05-14-1998
E1006	MARRY	JAAN	USA	II	30-04-1995

 

Usage of  [ ]  Wildcard

One set of characters specified between [ ] wildcard that will match any one characters in the specified in the location of the wildcard.

Now you want to select the persons with their FIRST NAME will match any one characters from the "EMPLOYEE" table, for this requirement you should use the following SELECT statement:

SELECT * FROM EMPLOYEE WHERE FIRSTNAME LIKE '[JA]%'

EMPLOYEE ID	FIRST NAME	LAST NAME	COUNTRY	GRADE	JIONED DATE
E1002	ALBERT	THOMAS	USA	I	24-09-2000
E1004	JARLESS	OLA	UK	III	07-09-2008

 

 Usage of  ^  Wildcard

 Any single character not within the specified range ([^F-P]) or set ([^MNOPQ])

Now you want to select the persons with their FIRST NAME will not match the character 'MA' from the "EMPLOYEE" table, for this requirement you should use the following SELECT statement:

SELECT * FROM EMPLOYEE WHERE FIRSTNAME LIKE '[^MA]%'

EMPLOYEE ID	FIRST NAME	LAST NAME	COUNTRY	GRADE	JIONED DATE
E1000	DEEPAK	GOTHARI	India	I	20- 08-1996
E1001	NIRMALA	RAJAN	India	II	13-01-2000
E1003	SYENDLA	ALVA	Canada	I	06-07-2006
E1004	JARLESS	OLA	UK	III	07-09-2008
E1005	HANSEN	DOVL	China	III	05-14-1998

 

Note: And if you want to join FIRSTNAME and LASTNAME Coolum use this SELECT statement.

SELECT (FIRSTNAME+' '+LASTNAME) AS EMPLOYEENAME FROM EMPLOYEE