Transact SQL 101 - Lesson 04_03_SQL.txt

-- Wildcard Examples
-- % - Represents 0 or Many Characters
SELECT customerid, firstname, lastname, companyname FROM saleslt.customer
WHERE companyname LIKE '%Bikes'

SELECT customerid, firstname, lastname, companyname FROM saleslt.customer
WHERE companyname LIKE 'Bikes%'

SELECT customerid, firstname, lastname, companyname FROM saleslt.customer
WHERE companyname LIKE '%Bikes%'

--Example 2
-- _ Represents a single character 
SELECT customerid, firstname, lastname, companyname FROM saleslt.customer
WHERE firstname LIKE 'Andre_'

SELECT customerid, firstname, lastname, companyname FROM saleslt.customer
WHERE firstname LIKE 'Kath_%'

--Example 3
-- [] Can specify a set of characters or a range
SELECT customerid, firstname, lastname, companyname FROM saleslt.customer
WHERE firstname LIKE 'Kath[le]%'

-- [^] Exclude a set of characters or a range
SELECT customerid, firstname, lastname, companyname FROM saleslt.customer
WHERE firstname LIKE 'Kath[^le]%'

--Example 4
--Insert Data
INSERT INTO saleslt.address (addressline1, city, stateprovince, countryregion, postalcode)
	VALUES ('No. 2_', 'Dallas', 'TX', 'United States', '75206')
INSERT INTO saleslt.address (addressline1, city, stateprovince, countryregion, postalcode)
	VALUES ('P.O. Box %', 'New York', 'NY', 'United States', '10022')

--Escape Characters
SELECT * FROM saleslt.address
WHERE addressline1 LIKE 'No. 2_%'

SELECT * FROM saleslt.address
WHERE addressline1 LIKE 'No. 2[_]%'

SELECT * FROM saleslt.address
WHERE addressline1 LIKE 'No. 2!_%' ESCAPE '!'


SELECT * FROM saleslt.address
WHERE addressline1 LIKE 'P.O. Box %'

SELECT * FROM saleslt.address
WHERE addressline1 LIKE 'P.O. Box [%]%'

SELECT * FROM saleslt.address
WHERE addressline1 LIKE 'P.O. Box !%%' ESCAPE '!'