Wildcards in SQL Server 2005

SQL wildcards are used for searching data’s in a database, which were used with LIKE operator.

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