案例语法错误

问题描述

您好我正在尝试使用mySQL执行此操作。我尝试使用SELECT IF和CASE来改变标题。每个标题描述在描述前面都有A;即使第二个单词以辅音开头。因此，我正在尝试查询说明，但编辑带有辅音的那些以An开头。

Hello I am attempting to do this with mySQL. I tried using SELECT IF and CASE to alter titles. Every title description has "A " in front of the description; even if the second word starts with a consonant. So I'm trying to query the descriptions but edit the ones with a leading consonant to start with "An ".

CASE
    WHEN description LIKE ('A A%') THEN CONCAT('An ', TRIM(LEADING 'A ' FROM description))
    WHEN description LIKE ('A E%') THEN CONCAT('An ', TRIM(LEADING 'A ' FROM description))
    WHEN description LIKE ('A I%') THEN CONCAT('An ', TRIM(LEADING 'A ' FROM description))
    WHEN description LIKE ('A O%') THEN CONCAT('An ', TRIM(LEADING 'A ' FROM description))
    WHEN description LIKE ('A U%') THEN CONCAT('An ', TRIM(LEADING 'A ' FROM description))
END;

所以这个特殊的代码只是给我错误代码：1064语法。

So this particular code was just giving me Error Code: 1064 syntax.

SELECT IF(description NOT LIKE ('A A%'), NULL, CONCAT('An ', TRIM(LEADING 'A ' FROM description))),
    IF(description NOT LIKE ('A E%'), null, CONCAT('An ', TRIM(LEADING 'A ' FROM description))),
    IF(description NOT LIKE ('A I%'), NULL, CONCAT('An ', TRIM(LEADING 'A ' FROM description))),
    IF(description NOT LIKE ('A O%'), NULL, CONCAT('An ', TRIM(LEADING 'A ' FROM description))),
    IF(description NOT LIKE ('A U%'), NULL, CONCAT('An ', TRIM(LEADING 'A ' FROM description)))
FROM film_text;

这个特殊的代码块返回单独的列但是我试图将所有这些查询到一列中。

This particular block of code returns separate columns however I'm trying to query all of this into one column.

我的IF块返回的内容：

What my IF block returns:

推荐答案

不要在<$中使用括号c $ c> LIKE 表达式。这可能是您看到的语法错误的原因。相反，尝试使用只有一个条件的 CASE 表达式：

Don't use parentheses around the terms in your LIKE expressions. This is probably the cause of the syntax error you are seeing. Instead, try using this CASE expression having just a single condition:

SELECT CASE WHEN UPPER(description) NOT LIKE 'A A%' AND
                 UPPER(description) NOT LIKE 'A E%' AND
                 UPPER(description) NOT LIKE 'A I%' AND
                 UPPER(description) NOT LIKE 'A O%' AND
                 UPPER(description) NOT LIKE 'A U%'
            THEN CONCAT('An ', TRIM(LEADING 'A ' FROM description))   -- replace A with An
            ELSE description      -- otherwise use original description
       END AS description
FROM yourTable

您还可以使用 REGEXP 此处使用负字符类 [^ aeiou] 来匹配标题不以元音开头：

You could also use REGEXP here with the negative character class [^aeiou] to match titles not beginning with a vowel:

SELECT CASE WHEN description REGEXP '^A [^aeiou]'
            THEN CONCAT('An ', TRIM(LEADING 'A ' FROM description))
            ELSE description
       END AS description
FROM yourTable

这篇关于案例语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！