/*
CIS 324

CHAPTER 7

The LIKE special operator, pp. 244-245
Find patterns within string variables (and in some cases dates)

'%a%' any row with string variable containing the letter a
'a%' any row with string variable starting with letter a
'%a' any row with string variable ending with letter a

This is a very useful technique for providing a search facility in an application.
Also, very useful for reports.

However, we should be careful in our  use of LIKE because it can
cause performance issues.
*/

/*
Search products by QuantityPerUnit: bottles, jars, or tins
Search products by ProductName: tofu, mix
Search products by starting letter(s): c, ch, che
*/

USE Northwind

SELECT *
FROM Products

--Find products in bottles
SELECT *
FROM Products
WHERE QuantityPerUnit LIKE '%bottles%'

--Search for Tofu
SELECT *
FROM Products
WHERE ProductName LIKE '%tofu%'

SELECT *
FROM Products
WHERE ProductName LIKE '%mix%'

--Products that start with 'ch'
SELECT *
FROM Products
WHERE ProductName LIKE 'ch%'


/*
Human Resources needs to send out a mailing to employees 
in the (206) area code regarding a new car pool initiative.
List the Full address of employees having area code (206)

*/

SELECT *
FROM Employees

SELECT *
FROM Employees
WHERE LTRIM(HomePhone) LIKE '(206)%'

/*
We could solve this query with some combination of string functions
such as 

LEFT (character_expression , integer_expression) 
RIGHT (character_expression , integer_expression) 
SUBSTRING (expression , start , length) 
*/

SELECT *
FROM Employees
WHERE LEFT(HomePhone, 5) = '(206)'

SELECT *
FROM Employees
WHERE SUBSTRING(HomePhone, 1, 5) = '(206)'

SELECT *
FROM Employees
WHERE HomePhone LIKE '(206)%'

SELECT 206, 555, 9857
SELECT '(' + CAST(206 AS NVARCHAR)+ ')' + 555, 9857