LIKE – SQL Server Syntax Example: LIKE – T-SQL Example

Purpose: – Illustrates the SQL Server syntax for the LIKE.

SYNTAX:

SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
[ INTO new_table ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] ]
[ GROUP BY ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ]

PURPOSE:
IN returns true or false indicating indicating whether expression matches a pattern.

Code Sample for LIKE:

 T-SQL | 
  |
?
 
 
/* LIKE example from http://idealprogrammer.com 
 PURPOSE: LIKE returns true or false indicating whether expression matches
          a pattern.
 
 SYNTAX: match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
 
 match_expression - expression that evaluates to character string
 
 Pattern:
 Wildcard character 	 Description 	 
 
%                        Any string of zero or more characters.
_ (underscore)           Any single character.
[ ]                      Any single character within the range ([b-e]) or set ([bcde]).
[^]                      Any single character not within the range ([^b-e]) or set ([^bcde]).
 
 escape_character is a character that is put in front of a wildcard character 
 to indicate that the wildcard should be interpreted as a regular character 
 and not as a wildcard. 
 
 
 
 SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column_list, ISNULL(check_expression, replacement_value)  
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] ] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
*/
 
 
CREATE TABLE people(
     ID          INT,
     firstname   VARCHAR (20),
     lastname    VARCHAR (20),
     statecode   VARCHAR (2),
     alive       BIT,
     height      INT )
 GO
 
 
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive) -- height is NULL
 VALUES (1,  'Paul', 'Revere', 'AL', 0)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (2,  'Pat', 'Lennon%', 'NY', 0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Peter', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 -- Select entire table
 SELECT 'Entire Table', * FROM people
 
 -- 1. Using % to select firstname that starts with P%
 SELECT 'Example #1' AS Example, firstname, lastname, statecode  
  FROM people 
  WHERE firstname LIKE 'P%' 
 
 GO
 
 -- 2. Using NOT LIKE to select firstname that starts with P%
 SELECT 'Example #2' AS Example, firstname, lastname, statecode  
  FROM people 
  WHERE firstname NOT LIKE 'P%'   
 
 GO
 
 -- 3. Using ESCAPE to select lastname that ends with %
 SELECT 'Example #3' AS Example, firstname, lastname, statecode  
  FROM people 
  WHERE lastname LIKE '%/%' ESCAPE '/'   
 
 GO
 
 -- 4. Using [] to select firstname that starts with P[ae]t%
 SELECT 'Example #4' AS Example, firstname, lastname, statecode  
  FROM people 
  WHERE firstname LIKE 'P[ae]t%' 
 
 GO
 
 
 
 
 
 DROP TABLE people;
 
 GO
 
 
 
 
 

Sample Output for LIKE Example