MySQL - REGEXP_INSTR() 函式

要檢索與特定模式匹配的 MySQL 資料庫中的所有記錄（無論是字串還是資料），透過 Node.js 程式，我們使用 **mysql2** 庫的 **query()** 函式執行 'SELECT' 語句，如下所示：

sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
con.query(sql);

要檢索與特定模式匹配的 MySQL 資料庫中的所有記錄（無論是字串還是資料），透過 Java 程式，我們使用 **JDBC** 函式 **executeUpdate()** 執行 'SELECT' 語句，如下所示：

String sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
statement.executeQuery(sql);

要檢索與特定模式匹配的 MySQL 資料庫中的所有記錄（無論是字串還是資料），透過 Python 程式，我們使用 MySQL **Connector/Python** 的 **execute()** 函式執行 'SELECT' 語句，如下所示：

sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT" 
cursorObj.execute(sql)

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});    

輸出

產生的輸出如下所示：

Select query executed successfully..!
Table records:
[ { RESULT: 9 } ]       

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_instr {
    public static void main(String[] args) {
        String url = "jdbc:mysql://:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
            rs = st.executeQuery(sql);
                  while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

輸出

獲得的輸出如下所示：

Result: 9

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_instr_query = f"SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT"
cursorObj.execute(regexp_instr_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_INSTR() Function:")
for row in results:
    position = row[0]
    if position > 0:
        print(f"The pattern 'To' found at position {position}")
    else:
        print("The pattern 'To' not found in the given string")
cursorObj.close()
connection.close()    

輸出

以下是上述程式碼的輸出：

Result of REGEXP_INSTR() Function:
The pattern 'To' found at position 9