How to search for special/reserved chars with MS SQL Server

How to search names with a . period or a , comma or any other special character? 

select firstnme, lastname 
from emps 
where firstnme like '%.%' or firstnme like '%,';

Data used

select firstnme as 'First Name', Lastname as 'Last Name'
from emps;

First Name   Last Name
------------ ---------------
CHRISTINE    HAAS
MICHAEL      THOMPSON
SALLY!       KWAN
JOHN         GEYER
IRVING@      STERN
EVA          PULASKI
EILEEN#      HENDERSON
THEODORE     SPENSER
VINCENZO.    LUCCHESSI
SEAN         O CONNELL
DOLORES,     QUINTANA
HEATHER      NICHOLLS
BRUCE        ADAMSON
ELIZABETH    PIANKA
MASATOSHI%   YOSHIMURA
MARILYN      SCOUTTEN
JAMES&       WALKER
DAVID        BROWN
WILLIAM*     JONES
JENNIFER]    LUTZ
JAMES        JEFFERSON
SALVATORE[   MARINO
DANIEL       SMITH
SYBIL\       JOHNSON
MARIA        PEREZ
ETHEL/       SCHNEIDER
JOHN         PARKER
PHILIP       SMITH
MAUDE        SETRIGHT
RAMLAL       MEHTA
WING         LEE
JASON        GOUNOT

(32 row(s) affected)

How to search for @ or ]

Search for first names that contain the special character: @ or that end with: ] 

select firstnme as 'First Name', Lastname as 'Last Name'
from emps 
where firstnme like '%@%' or firstnme like '%]';

First Name   Last Name
------------ ---------------
IRVING@      STERN
JENNIFER]    LUTZ

(2 row(s) affected)

Search for wildcards

Search for all the first names that begin with an E. 

select firstnme as 'First Name', Lastname as 'Last Name'
from emps 
where firstnme like 'E%' 
order by firstnme;

First Name   Last Name
------------ ---------------
EILEEN#      HENDERSON
ELIZABETH    PIANKA
ETHEL/       SCHNEIDER
EVA          PULASKI

(4 row(s) affected)

Search for escape characters

  • The wildcard is the %, therefore it's a reserved character.
  • Search for first names that contain the percent %. The percent is used by the like command to mean the wildcard, so you will need to escape it. The escape can be any valid ASCII character, most often it is the '\' backslash.
  • You specify the escape character by using the word escape followed by what you want to use as an escape combination. 
select firstnme as 'First Name', Lastname as 'Last Name'
from emps 
where firstnme like '%+%' escape '+';

First Name   Last Name
------------ ---------------
MASATOSHI%   YOSHIMURA

(1 row(s) affected)