/* Advanced SQL Programming			      Fall 2002 */
/*								*/
/* 	In-class examples using IS NULL and LIKE - 9/30/2002	*/
/*								*/
1> use pubs2
2> go
/*								*/
/* Find titles with null price.					*/
/*								*/
1> select title_id , price from titles
2> where price is null
3> go
 title_id price                    
 -------- ------------------------ 
 MC3026                       NULL 
 PC9999                       NULL 

(2 rows affected)
/*								*/
/* Find wildcard character (% in this case) in a string.	*/
/* Break notes col. into shorter segments for display purposes. */
/*								*/
1> select title_id, substring(notes, 1, 70),
2> substring(notes, 71, 70),
3> substring(notes, 141, char_length(notes))
4>  from titles
5> where notes like '%\%%' escape '\'
6> go
 title_id

 -------- 
	---------------------------------------------------------------------- 
	---------------------------------------------------------------------- 
	-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 TC7777  
	 Detailed instructions on improving your position in life by learning h
	 ow to make authentic Japanese sushi in your spare time.  5-10% increas
         e in number of friends per recipe reported from beta test.            

(1 row affected)
/*								*/
/* Use LIKE  to find titles having anything to with cooking.	*/
/*								*/
1> select title from titles
2> where type like '%cook%'
3> go

	 title                                                                            
 
	-------------------------------------------------------------------------------- 

	 Silicon Valley Gastronomic Treats                                                

	 The Gourmet Microwave                                                            

	 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean                  

	 Fifty Years in Buckingham Palace Kitchens                                        

	 Sushi, Anyone?                                                                   

(5 rows affected)
/* 								*/
/* No rows selected in next example:  Sybase is case-sensitive. */
/*								*/
1> select title from titles
2> where title like '%cook%'
3> go

	 title                                                                            
 
	-------------------------------------------------------------------------------- 

(0 rows affected)
/*								*/
/* Use upper() (or lower()) to work around case-sensitivity.	*/
/*								*/
1> select title from titles
2> where upper(title) like '%COOK%'
3> go

	 title                                                                            
 
	-------------------------------------------------------------------------------- 

	 Cooking with Computers: Surreptitious Balance Sheets                             

	 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean                  

	 The Psychology of Computer Cooking                                               

(3 rows affected)
/*								*/
/* Alternative:  Search for either upper or lower case using [] */
/*								*/
1> select title from titles
2> where title like '%[C,c]ook%'
3> go

	 title                                                                            
 
	-------------------------------------------------------------------------------- 

	 Cooking with Computers: Surreptitious Balance Sheets                             

	 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean                  

	 The Psychology of Computer Cooking                                               

(3 rows affected)