/* Find package name */
SELECT DISTINCT 
	   MIN(qry.DISPLAY_ORDER) OVER (PARTITION BY qry.PKG_NAME) AS DISPLAY_MIN,
	   qry.PKG_ID,
	   qry.PKG_NAME
  FROM (
  
  		/* Exact match */
  		SELECT 1 AS DISPLAY_ORDER,
			   pkg.PKG_ID,
			   pkg.PKG_NAME
		  FROM PACKAGES pkg
		 WHERE UPPER(pkg.PKG_NAME) = UPPER(:KEYWORD)
		 
		UNION
		  
		/* Starts with */  
		SELECT 2 AS DISPLAY_ORDER,
			   pkg.PKG_ID,
			   pkg.PKG_NAME
		  FROM PACKAGES pkg
		 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER(:KEYWORD) ||'%'
		 
		UNION
		  
		/* Ends with */  
		SELECT 3 AS DISPLAY_ORDER,
			   pkg.PKG_ID,
			   pkg.PKG_NAME
		  FROM PACKAGES pkg
		 WHERE UPPER(pkg.PKG_NAME) LIKE '%'|| UPPER(:KEYWORD) 

		UNION
		  
		/* Middle of word */  
		SELECT 4 AS DISPLAY_ORDER,
			   pkg.PKG_ID,
			   pkg.PKG_NAME
		  FROM PACKAGES pkg
		 WHERE UPPER(pkg.PKG_NAME) LIKE '%'|| UPPER(:KEYWORD) ||'%' 		 
		 
       ) qry
ORDER BY DISPLAY_MIN, UPPER(qry.PKG_NAME) 
 	   