Case Syntax Errors

Question

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;

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.

What my IF block returns:

Your initial `CASE` expression looks on the right track. What is the output from that? — Tim Biegeleisen, Jan 18 '17 at 02:22
May I suggest you upvote all answers you find helpful, and accept the one you find most useful? I see you've asked a couple of questions, but never upvoted nor accepted an answer. This helps the community, and other users who may have similar questions. — alttag, Jan 18 '17 at 02:57

Tim Biegeleisen · Answer 1 · 2017-01-18T02:53:44.750

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

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

Thank you both for the responses! I will look at these! – user6749426 Jan 18 '17 at 02:39 — user6749426, Jan 18 '17 at 02:39

alttag · Answer 2 · 2017-01-18T02:44:22.753

Your question is really several questions.

To get data from multiple "columns" returned as a single column, you'd use separate queries and use UNION, between them.

But this isn't really your issue.

It might be better to do the check (the "if") in the WHERE clause, and the replacement only once in SELECT.

This is untested, but may get you closer to what you want:

SELECT 
  CONCAT('An ', TRIM(LEADING 'A ' FROM description)) AS descrip
FROM film_text
WHERE description REGEXP '^A [aeiou]';

The REGEXP does the check you were trying to perform in your CASE, and limits the result set to those rows only.

To do the whole thing, and return all descriptions, you can combine the REGEX syntax with the CASE you initially tried.

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

The edited addition should address grabbing all. Case sensitivity depends first on table collation (not provided). Also, [from the docs](http://dev.mysql.com/doc/refman/5.7/en/regexp.html) "REGEXP is not case sensitive, except when used with binary strings." — alttag, Jan 18 '17 at 02:35
Thank you very much for this breakdown. I am very new to SQL and this is helping make sense of things — user6749426, Jan 18 '17 at 02:42

Case Syntax Errors

2 Answers2

Linked