Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table myTable ("Name" text);
insert into myTable values
('2abc-def'),('3abcdef'),('1abc def')
returning *;

CREATE TABLE




Name


2abc-def

3abcdef

1abc def



INSERT 0 3


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



SELECT 3


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 1 --you could also pick a column by its position


StrippedName


1abc

2abc

3abcdef



SELECT 3


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 --won't work because it's folded to lowercase
from myTable
order by "StrippedName";

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


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--won't work because it's folded to lowercase

ERROR:  column "strippedname" does not exist
LINE 9: order by StrippedName--won't work because it's folded to low...
                 ^