如何编写SQL查询匹配指定字符串与Col1并输出最大值

Let's break down how to solve this problem: we need to filter rows where Col1's value is a substring of the target string '123456789', then pull the maximum value from those matches.

Step-by-Step Approach #

1. Filter matching rows: Use your database's built-in string position function to check if each Col1 value exists within the target string.
2. Get the maximum value: Apply the MAX() aggregate function to the filtered Col1 results to grab the largest matching value.

SQL Queries for Common Databases #

Below are working examples tailored to major database systems:

MySQL / MariaDB

SELECT MAX(Col1) AS matched_max_value
FROM your_table_name
WHERE LOCATE(Col1, '123456789') > 0;

• LOCATE(substr, str) returns the starting position of substr in str; any result greater than 0 means the substring was found.

PostgreSQL

SELECT MAX(Col1) AS matched_max_value
FROM your_table_name
WHERE POSITION(Col1 IN '123456789') > 0;

• POSITION(substr IN str) works like MySQL's LOCATE, returning the substring's position or 0 if it isn't present.

SQL Server

SELECT MAX(Col1) AS matched_max_value
FROM your_table_name
WHERE CHARINDEX(Col1, '123456789') > 0;

• CHARINDEX(substr, str) returns the starting index of substr in str; a return value of 0 means no match was found.

Oracle

SELECT MAX(Col1) AS matched_max_value
FROM your_table_name
WHERE INSTR('123456789', Col1) > 0;

• INSTR(str, substr) searches for substr within str and returns its position; 0 indicates no match exists.

Verification #

For your sample data where Col1 contains 123, 456, 789, all three values are valid substrings of '123456789'. The MAX() function will correctly return 789 as the largest value.

Quick note: If Col1 is stored as a string type, lexicographical order aligns with numerical order for these three-digit values. For variable-length numeric strings, you might want to cast Col1 to a numeric type first (e.g., MAX(CAST(Col1 AS UNSIGNED)) in MySQL) to ensure accurate numerical sorting.

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