The Java Developers Almanac 1.4


Order this book from Amazon.

   
Home > List of Packages > java.sql  [73 examples] > Retrieving Data  [7 examples]

e257. Matching with Wildcards in a SQL Statement

SQL provides wildcard matching of text using the LIKE clause. Here is a SQL statement that uses a LIKE clause and a wildcard character. This SQL statement will find all rows with names that start with Pat.
    SELECT * FROM my_table WHERE name LIKE 'Pat%'
There are two wildcard characters available. The underscore (_) matches any character. The percent sign (%) matches zero or more characters.
    try {
        // Create a statement
        Statement stmt = connection.createStatement();
    
        // Select the row if col_string contains the word pat
        String sql = "SELECT * FROM my_table WHERE col_string LIKE '%pat%'";
    
        // Select the row if col_string ends with the word pat
        sql = "SELECT * FROM my_table WHERE col_string LIKE 'pat%'";
    
        // Select the row if col_string starts with abc and ends with xyz
        sql = "SELECT * FROM my_table WHERE col_string LIKE 'abc%xyz'";
    
        // Select the row if col_string equals the word pat%
        sql = "SELECT * FROM my_table WHERE col_string LIKE 'pat\\%'";
    
        // Select the row if col_string has 3 characters and starts with p and ends with t
        sql = "SELECT * FROM my_table WHERE col_string LIKE 'p_t'";
    
        // Select the row if col_string equals p_t
        sql = "SELECT * FROM my_table WHERE col_string LIKE 'p\\_t'";
    
        // Execute the query
        ResultSet resultSet = stmt.executeQuery(sql);
    } catch (SQLException e) {
    }

 Related Examples
e251. Getting Rows from a Database Table
e252. Getting Data from a Result Set
e253. Determining If a Fetched Value Is NULL
e254. Getting the Column Names in a Result Set
e255. Getting the Number of Rows in a Database Table
e256. Getting BLOB Data from a Database Table

See also: Batching    Connections    Database Meta Data    Deleting Data    Drivers    Importing and Exporting    Inserting and Updating Data    Oracle OBJECTs    Oracle VARRAYs    Procedures and Functions    Scrollable Result Sets    Tables    Updatable Result Sets   


© 2002 Addison-Wesley.