LIKE – SQL Server Syntax Example: LIKE – T-SQL Example
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:
/* 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 |