SQL混合字母数字列平均值计算：字母转数字代码失效问题排查

Hey there! Let's get this grade average calculation sorted out for you. The issue with your current code is twofold: your DECLARE syntax is incorrect, and you aren't handling cases where removing letters leaves an empty string (which will throw an error when casting to an integer). Here's how to fix this properly:

First, let's break down the problems in your original code:

• You can't mix variable declaration and the cast/replace logic in a single DECLARE line like that.
• If a grade entry is only letters (e.g., just 'A' with no numbers), replacing all letters leaves an empty string—casting that to INT will fail.

Below are corrected solutions for the most common databases:

For SQL Server #

Use TRY_CAST to safely convert the cleaned string to an integer (it returns NULL if conversion fails, and AVG() will automatically ignore NULL values):

SELECT AVG(TRY_CAST(REPLACE(REPLACE(REPLACE(grades.grade, 'A', ''), 'B', ''), 'C', '') AS INT)) AS average_grade
FROM grades;

If you need to reuse the cleaned grade value elsewhere in your script, use a CTE (Common Table Expression) instead:

WITH CleanedGrades AS (
    SELECT TRY_CAST(REPLACE(REPLACE(REPLACE(grade, 'A', ''), 'B', ''), 'C', '') AS INT) AS cleanGrade
    FROM grades
)
SELECT AVG(cleanGrade) AS average_grade
FROM CleanedGrades;

For MySQL #

MySQL doesn't have TRY_CAST pre-8.0.17, so use NULLIF to turn empty strings into NULL before casting (this avoids converting empty strings to 0, which would skew your average):

SELECT AVG(CAST(NULLIF(REPLACE(REPLACE(REPLACE(grades.grade, 'A', ''), 'B', ''), 'C', ''), '') AS UNSIGNED)) AS average_grade
FROM grades;

If you're using MySQL 8.0.17 or later, you can use TRY_CAST for simpler code:

SELECT AVG(TRY_CAST(REPLACE(REPLACE(REPLACE(grades.grade, 'A', ''), 'B', ''), 'C', '') AS UNSIGNED)) AS average_grade
FROM grades;

For PostgreSQL #

PostgreSQL's TRY_CAST works just like SQL Server's, making the code straightforward:

SELECT AVG(TRY_CAST(REPLACE(REPLACE(REPLACE(grades.grade, 'A', ''), 'B', ''), 'C', '') AS INTEGER)) AS average_grade
FROM grades;

Quick Tips: #

• If your grades include other letters (like D, F, or +/– symbols), just add additional REPLACE calls to strip those out too.
• TRY_CAST (or NULLIF + CAST) ensures only valid numeric values are included in your average, skipping any entries that can't be converted to a number.

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