Subversion Repositories DevTools

/* 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)