Question 309870 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Order by case statement that extracts substring from column

I have the below SQL that takes only data from the Name column up to the point where a space or hyphen appears. I would like to order the results by the output of the case statement but can’t find a way to do it.

select 

case 
when strpos("Name",' ') > 0 then substr("Name", 0, strpos("Name", ' '))
when strpos("Name",'-') > 0 then substr("Name", 0, strpos("Name", '-'))
else "Name"
end as StrippedName

from myTable

order by "StrippedName"
Tags:

2

Answers


  1. 0

    You could use a subquery:

    select  *
from    (
        select  case ... end as col1
        from    ...
        ) as SubQueryAlias
order by
        col1
    Login or Signup to reply.
  2. 0

    What you’re doing is perfectly fine as long as you double-quote the name of the case result in both the select list and the order by. Mismatching the quotes makes the case produce a column named strippedname, while order by looks for a
    StrippedName and doesn’t find it, throwing an

    ERROR:  column "StrippedName" does not exist
LINE 9: order by "StrippedName";
                 ^

    If you drop the quotes in both places or use them in both, it’ll work just fine: demo at db<>fiddle

    create table myTable ("Name" text);
insert into myTable values ('2abc-def'),('3abcdef'),('1abc def')

select case 
         when strpos("Name",' ') > 0 
           then substr("Name", 0, strpos("Name", ' '))
         when strpos("Name",'-') > 0 
           then substr("Name", 0, strpos("Name", '-'))
         else "Name"
       end as "StrippedName" --without " it was folded to "strippedname"
from myTable
order by "StrippedName"--was looking for a case-sensitive "StrippedName"
--order by 1 --you could also pick a column by its position
    StrippedName
    1abc
    2abc
    3abcdef

    A reminder from the doc on PostgreSQL lexical structure:

    Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case.

    You can also use a column position instead of its name: order by 1

    Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

    It’s possible to reduce your entire case down to a single substring with a POSIX regular expression:

    select regexp_substr("Name",'([^ -]+)') as "StrippedName"
from myTable 
order by "StrippedName"

    It’s looking for a sequence of characters that aren’t a hyphen - or a space , so it’ll return everything up to the first position of either (or the whole thing if neither is found). Using a + rather than a * makes it skip leading delimiters, looking for non-empty matches.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search