MySQL :Regex Example

In this tutorial we are going to discuss about regex with example in JDBC.

In this tutorial we are going to discuss about regex with example in JDBC.

MySQL :Regex Example

In this tutorial we are going to discuss about regex with example in JDBC.

MySQL : Regex

You can use Regular Expressions in mysql with the keyword REGEXP. It is better way to solve the complex comparison problems. Its functionality is just like the LIKE operator but it uses a POSIX Extended Regular Expression (ERE) in place of - and % wildcards. Basically, it is used as hardcoded Regex under any condition.

Meta characters used by Regular Expression -

. It matches any single character, but does not newline.
* It matches zero or more instance of the preceding string.
+ It matches one or more instance of the preceding string.
? It matches zero or one instance of the preceding string.
[abc] It matches any of a,b or c that is one of enclosed characters.
[^abc] It matches any character except a,b and c.
[a-z] It matches lowercase character.
[A-Z] It matches uppercase character.
[0-9] It matches any digit.
^ It anchors the match from the beginning.
$ It anchors the match to the end.
| It is used to separates alternatives.
{n,m} It defines the limit of string that is string occurrence at least n times but not exceed to m.
{n} Occurrence of String must be n times.
{n,} String must occur at least n times.
[[:<:]] It matches beginning of words
[[:>:]] It matches ending of words
[:class:] It matches a character class i.e.,
[:alpha:] for letters
[:space:] for whitespace
[:punct:] for punctuation
[:upper:] for upper case letters

MySQL provide a backslash (\) character as an escape character. You can use it in regular expression by writing double backslash(\\).

Example : In this example we are using regular expression to select name of such employees whose name starts with 'j' character.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

class JDBCRegex {
	public static void main(String[] args) {
		System.out.println("JDBC Regex Example...");
		Connection conn = null;
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "employees";
		String driverName = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "root";
		Statement statement = null;
		ResultSet rs;
		try {
			Class.forName(driverName);
			conn = DriverManager
					.getConnection(url + dbName, userName, password);
			statement = conn.createStatement();
			String sql = "SELECT name FROM employee WHERE name REGEXP '^j';";
			rs = statement.executeQuery(sql);
			System.out.println("Name starting with 'j' character :  ");
			while (rs.next()) {

				System.out.println(rs.getString("name"));
			}
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Output :

JDBC Regex Example...
Name starting with 'j' character :  
Jackson
Joly

Ads