0

I am attempting to generate a new field based on an existing field where some of the entries contain different special characters. (ie. *, ') The special characters are at the end of the string, which is either second or third position.

I am NEW to SQL but not new to data. I am using a CASE WHEN statement. I have tried several approaches and several other commands within the CASE statement.

what I want is:

SELECT *
    CASE WHEN grde_code_mid LIKE '[*]' THEN 'Remedial'
         WHEN grde_code_mid LIKE '[']' THEN 'Continuing'
         ELSE NULL
    END AS class_type
FROM grde_tble

I keep getting the same error: "FROM keyword not found where expected". I expect to have all 3 returns in the new field.

jarlh
  • 42,561
  • 8
  • 45
  • 63
DataGirl
  • 1

3 Answers3

0

If you're looking for ' character you should escape it.

Change 

WHEN grde_code_mid LIKE '[']' THEN 'Continuing'

by

WHEN grde_code_mid LIKE '['']' THEN 'Continuing'

Have a look at this question: How do I escape a single quote in SQL Server?

McNets
  • 10,352
  • 3
  • 32
  • 61
0

There are several issues with your query:

  • you are missing a comma in the SELECT clause between * and the CASE expression (this is causing the error that you are currently getting)
  • the bracket notations is only supported in SQL-Server; if you want to match on strings that end with * in a portable manner, you need expression ... LIKE '%*', not LIKE '[*]'
  • single quotes embedded in a string need to be escaped
  • SELECT *, other_field FROM ... is not supported on all RDBMS (and, as commented by jarhl, actually isn't standard ANSI SQL notation); you usually need to prefix the * with the table name or alias

Consider:

SELECT 
    g.*,
    CASE 
        WHEN grde_code_mid LIKE '%*' THEN 'Remedial'
        WHEN grde_code_mid LIKE '%''' THEN 'Continuing'
        ELSE NULL
    END AS class_type
FROM grde_tble g
GMB
  • 216,147
  • 25
  • 84
  • 135
0

Thank you all. I am still getting the hang of the language and proper terms. I am using Oracle DB. Also, yes, I did need to reference the table in the select statement (SELECT tablename.*). Found a work around:

CASE WHEN regexp_like(grde_code_mid, '[*]') THEN 'Remedial' WHEN regexp_like(grde_code_mid, '['']') THEN 'Continuing' ELSE NULL END AS special_class

DataGirl
  • 1