​sql_export_columns(sqlText)
sql_export_columns(sqlText, dbType)
sql_export_columns(sqlText, dbType, throwError)​
  • Description: returns all the columns in sqlText. These columns are separated by commas (,). An attempt is made to find the tables to which the columns belong. The column name is UNKNOWN if no table is found.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_export_columns(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_columns(a.sql_text)                                                                                                                                                                                                                                                                                                                  |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | UNKNOWN.s_acctbal,UNKNOWN.s_name,UNKNOWN.n_name,UNKNOWN.p_partkey,UNKNOWN.p_mfgr,UNKNOWN.s_address,UNKNOWN.s_phone,UNKNOWN.s_comment,UNKNOWN.ps_partkey,UNKNOWN.s_suppkey,UNKNOWN.ps_suppkey,UNKNOWN.p_size,UNKNOWN.p_type,UNKNOWN.s_nationkey,UNKNOWN.n_nationkey,UNKNOWN.n_regionkey,UNKNOWN.r_regionkey,UNKNOWN.r_name,UNKNOWN.ps_supplycost |​

SQL_EXPORT_FUNCTIONS

​sql_export_functions(sqlText)
sql_export_functions(sqlText, dbType)
sql_export_functions(sqlText, dbType, throwError)​
  • Description: returns the names of all functions in sqlText. The names of the functions are separated by commas (,). An attempt is made to find the tables to which the columns belong. The column name is UNKNOWN if no table is found.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_export_functions(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
    +----------------------------------+
    | sql_export_functions(a.sql_text) |
    +----------------------------------+
    | min                              |​

SQL_EXPORT_PREDICATE_COLUMNS

sql_export_predicate_columns(sqlText)
sql_export_predicate_columns(sqlText, dbType)
sql_export_predicate_columns(sqlText, dbType, throwError)​
  • Description: returns the names of all columns in predicative expressions of sqlText. The names are separated by commas (,). An attempt is made to find the tables to which the columns belong. The column name is UNKNOWN if no table is found.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_export_predicate_columns(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_predicate_columns(a.sql_text)                                                                                                                                                                                     |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | UNKNOWN.p_partkey,UNKNOWN.ps_partkey,UNKNOWN.s_suppkey,UNKNOWN.ps_suppkey,UNKNOWN.p_size,UNKNOWN.p_type,UNKNOWN.s_nationkey,UNKNOWN.n_nationkey,UNKNOWN.n_regionkey,UNKNOWN.r_regionkey,UNKNOWN.r_name,UNKNOWN.ps_supplycost |​

SQL_EXPORT_PREDICATES

​sql_export_predicates(sqlText)
sql_export_predicates(sqlText, dbType)
sql_export_predicates(sqlText, dbType, compactValues)
sql_export_predicates(sqlText, dbType, compactValues, throwError)​
  • Description: returns all element arrays in predicative expressions of sqlText. The arrays are separated by commas (,). An attempt is made to find the tables to which the columns belong. The column name is UNKNOWN if no table is found.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • compactValues: specifies whether the values in predicative expressions are returned as an array. The value type is BOOLEAN. If the value is TRUE, the values in predicative expressions are returned as an array. This parameter is optional.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_export_predicates(a.sql_text, 'mysql', true)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_predicates(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                  |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | [["UNKNOWN","p_partkey","=",null],["UNKNOWN","ps_partkey","=",null],["UNKNOWN","s_suppkey","=",null],["UNKNOWN","ps_suppkey","=",null],["UNKNOWN","p_size","=",35],["UNKNOWN","p_type","LIKE","%NICKEL"],["UNKNOWN","s_nationkey","=",null],["UNKNOWN","n_nationkey","=",null],["UNKNOWN","n_regionkey","=",null],["UNKNOWN","r_regionkey","=",null],["UNKNOWN","r_name","=",["MIDDLE EAST","MIDDLE EAST"]],["UNKNOWN","ps_supplycost","IN",null]] |​

SQL_EXPORT_SELECT_LIST_COLUMNS

​sql_export_select_list_columns(sqlText)
sql_export_select_list_columns(sqlText, dbType)
sql_export_select_list_columns(sqlText, dbType, throwError)​
  • Description: returns a list of column names (including those of subqueries) in the SELECT clauses of sqlText. These names are separated by commas (,). An attempt is made to find the tables to which the columns belong. The column name is UNKNOWN if no table is found.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_export_select_list_columns(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_select_list_columns(a.sql_text)                                                                                                                 |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | UNKNOWN.s_acctbal,UNKNOWN.s_name,UNKNOWN.n_name,UNKNOWN.p_partkey,UNKNOWN.p_mfgr,UNKNOWN.s_address,UNKNOWN.s_phone,UNKNOWN.s_comment,UNKNOWN.ps_supplycost |​

SQL_EXPORT_TABLES

​sql_export_tables(sqlText)
sql_export_tables(sqlText, dbType)
sql_export_tables(sqlText, dbType, throwError)​
  • Description: returns the names of all tables in sqlText. These names are separated by commas (,).

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_export_tables(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      | sql_export_tables(a.sql_text)        |
      +--------------------------------------+
      | part,supplier,partsupp,nation,region |​

SQL_FORMAT

​sql_format(sqlText)
sql_format(sqlText, dbType)
sql_format(sqlText, dbType, throwError)​
  • Description: formats SQL statements in sqlText.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_format(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_format(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
          , s_address, s_phone, s_comment
      FROM part, supplier, partsupp, nation, region
      WHERE p_partkey = ps_partkey
          AND s_suppkey = ps_suppkey
          AND p_size = 35
          AND p_type LIKE '%NICKEL'
          AND s_nationkey = n_nationkey
          AND n_regionkey = r_regionkey
          AND r_name = 'MIDDLE EAST'
          AND ps_supplycost IN (
              SELECT min(ps_supplycost)
              FROM partsupp, supplier, nation, region
              WHERE s_suppkey = ps_suppkey
                  AND s_nationkey = n_nationkey
                  AND n_regionkey = r_regionkey
                  AND r_name = 'MIDDLE EAST'
          )
      ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
      LIMIT 100; |​

SQL_PARAMS

​sql_params(sqlText)
sql_params(sqlText, dbType)
sql_params(sqlText, dbType, throwError)​
  • Description: extracts the literal values of SQL statements in sqlText.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_params(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------------------------+
      | sql_params(a.sql_text)                         |
      +------------------------------------------------+
      | [35,"%NICKEL","MIDDLE EAST","MIDDLE EAST",100] |​

SQL_PATTERN

​sql_pattern(sqlText)
sql_pattern(sqlText, dbType)
sql_pattern(sqlText, dbType, throwError)​
  • Description: extracts the SQL pattern parameterized by an SQL statement. In the pattern, the literal is replaced with ?.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_pattern(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_pattern(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
          , s_address, s_phone, s_comment
      FROM part, supplier, partsupp, nation, region
      WHERE p_partkey = ps_partkey
          AND s_suppkey = ps_suppkey
          AND p_size = ?
          AND p_type LIKE ?
          AND s_nationkey = n_nationkey
          AND n_regionkey = r_regionkey
          AND r_name = ?
          AND ps_supplycost IN (
              SELECT min(ps_supplycost)
              FROM partsupp, supplier, nation, region
              WHERE s_suppkey = ps_suppkey
                  AND s_nationkey = n_nationkey
                  AND n_regionkey = r_regionkey
                  AND r_name = ?
          )
      ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
      LIMIT ? ; |​

SQL_PATTERN_HASH

​sql_pattern_hash(sqlText)
sql_pattern_hash(sqlText, dbType)
sql_pattern_hash(sqlText, dbType, throwError)​
  • Description: extracts the SQL pattern parameterized by an SQL statement. In the pattern, the literal is replaced with ?.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: VARCHAR.

  • Example:

    ​  SELECT sql_pattern_hash(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------+
      | sql_pattern_hash(a.sql_text) |
      +------------------------------+
      |           925870115679910184 |​

SQL_SYNTAX_CHECK

​sql_syntax_check(sqlText)
sql_syntax_check(sqlText, dbType)
sql_syntax_check(sqlText, dbType, throwError)​
  • Description: checks the syntax of an sqlText statement. If the value 1 is returned, the syntax is correct. If the value 0 is returned, the syntax is incorrect.

  • Parameters:

    • sqlText: the data type of SQL texts, which is VARCHAR. This parameter is required.

    • dbType: the data type of SQL dialects, which is VARCHAR. This parameter is optional. Default value: MySQL.

    • throwError: specifies whether an error is returned when an SQL statement is invalid. The value type is BOOLEAN. When an SQL statement is invalid, if this parameter is set to TRUE, an error is returned. If this parameter is set to FALSE, NULL is returned in this case. This parameter is optional.

  • Return value type: BOOLEAN.

  • Example:

    ​  SELECT sql_syntax_check(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------+
      | sql_syntax_check(a.sql_text) |
      +------------------------------+
      |                            1 |​