Using a Position Function in a Case Function - PostgreSQL

Question

I'm new to SQL and Postgres, so hopefully this isn't too hard to figure out for all of you.

I'm trying to use a Position function within a CASE statement, but I keep getting the error "ERROR: Syntax error at or near ""Project"". LINE 2: CASE WHEN position('(' IN "Project") >0 THEN".

I've used this position function before and it worked fine, so I'm confused what the problem is here. I've also tried the table name, such as "xyztable.Project" and "Project" - both without quotation marks.

Here is the entire statement:

SELECT "Project",
CASE WHEN postion('(' IN "Project") >0 THEN
    substring("Project",position('(' IN "Project")+1,position(')' IN "Project")-2)
CASE WHEN postion('('IN "Project") IS NULL THEN
    "Project"
END
FROM "2015Budget";

As I haven't gotten to past the second line of this statement, if anyone sees anything that would prevent this statement from running correctly, please feel free to point it out.

New Statement:

SELECT "Project",
CASE 
  WHEN position('(' IN "Project") >0 THEN
    substring("Project",position('(' IN "Project")+1,position(')' IN "Project")-2)
  WHEN position('('IN "Project") IS NULL THEN
    "Project"
END
FROM "2015Budget";

Thank you for your help!!

Nick Barnes · Accepted Answer · 2016-02-29 05:21:58Z

1

The error is due to a simple typo - postion instead of position.

You would generally get a much more comprehensible error message in situations like this (e.g. "function postion(text,text) does not exist"). However, the use of function-specific keywords as argument separators (as mandated by the SQL standard) makes this case much more difficult for the parser to cope with.

After fixing this, you'll run into another error. Note that the general form of a multi-branch CASE expression is:

CASE
  WHEN <condition1> THEN <value1>
  WHEN <condition2> THEN <value2>
  ...
END

answered Feb 29, 2016 at 5:21

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jones-chris Over a year ago

Ah, I see. Guess I was working on this too late into the night:).

jones-chris Over a year ago

I added the new statement to the original question. However, no rows are returned and I get two columns, Project and Case. Instead, I want one column returned called Project Number that is returns a substring on the Project column if the Project field contains "(", otherwise I want it to just return the Project field. Do you know how to do this?

Nick Barnes Over a year ago

It's returning a second column because you have "Project" listed on the first line of your query. You can name the other column by putting AS "Project Number" at the end of the CASE statement. If this query is returning zero rows, it's because your "2015Budget" table is empty.

jones-chris Over a year ago

Ah, perfect! Thank you! Also, I changed the WHEN position('(' IN "Project") IS NULL clause to WHEN position('(' IN "Project") =0 and it pulled the data I wanted. And you were right about not having any data in the "2015Budget" table. I had deleted the data just before trying this query. Oops! Like I said - I was up too late last night!

Collectives™ on Stack Overflow

Using a Position Function in a Case Function - PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related