MySQL中筛选trans_descr含8位连续整数的行（仅SELECT权限）

Got it, since you only have SELECT permissions and need to pull rows where trans_descr contains an 8-digit integer, regular expressions are your best bet here. The exact syntax varies slightly by database system, so I'll cover the most common ones below:

MySQL/MariaDB #

Use the REGEXP operator to match 8 consecutive digits anywhere in the string:

SELECT trans_date, trans_descr, response_code
FROM your_table_name
WHERE trans_descr REGEXP '[0-9]{8}';

PostgreSQL #

PostgreSQL uses the ~ operator for regex matching (you can also use REGEXP_LIKE for clearer syntax):

SELECT trans_date, trans_descr, response_code
FROM your_table_name
WHERE trans_descr ~ '\d{8}';
-- OR
WHERE REGEXP_LIKE(trans_descr, '\d{8}');

SQL Server #

Since SQL Server's standard LIKE doesn't support quantifiers, use PATINDEX to search for 8 consecutive digits (works for all SQL Server versions, no extra permissions needed):

SELECT trans_date, trans_descr, response_code
FROM your_table_name
WHERE PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', trans_descr) > 0;

If you're on SQL Server 2017 or later, you can also use REGEXP_LIKE (requires compatibility level 140+):

SELECT trans_date, trans_descr, response_code
FROM your_table_name
WHERE REGEXP_LIKE(trans_descr, '[0-9]{8}');

Oracle #

Use Oracle's built-in REGEXP_LIKE function:

SELECT trans_date, trans_descr, response_code
FROM your_table_name
WHERE REGEXP_LIKE(trans_descr, '[0-9]{8}');

Why this works #

All these queries target any occurrence of 8 consecutive numeric digits in the trans_descr field. This will include your expected rows (1,2,3,4) which have values like Acct Trsf:17799788baliek and TEST/L17699470bajczy, while excluding rows 5 and 6 which don't contain an 8-digit sequence.

内容的提问来源于stack exchange，提问作者brucewayne