Extract first word to first space - POSTGRES

Question

I would like to extract only the first word to the first space.

I used the following query:

select upper(substring(substring(descripcion,28),0,length(substring(descripcion,28))-position(' ' in reverse(substring(descripcion,28)))+1)) from evento where descripcion ~ 'Act. datos:Actualización';

But it gives me back everything, not just the first word before the first space.

How can I get the following result

1. John
2. Elena
3. Maria
4. Marcus
5. Mario
6. Ana
7. Pedro

etc.

Answer 1

Use split_part with space as delimiter to get the first string, e.g.

SELECT split_part('CARMEN SANDIEGO',' ',1);

 split_part 
------------
 CARMEN

So in your case it should be something like

SELECT 
  upper(
   split_part(
    trim(substring(descripcion,28)),' ',1))
FROM evento
WHERE descripcion ~ 'Act. datos:Actualización';

Demo: db<>fiddle

Answer 2

Use regexp_replace:

select upper(regexp_replace(
    descripcion,
    '^Act\. datos:Actualización de ([a-z]+).+$', 
    '\1'))
  from evento 
  where descripcion ~ '^Act\. datos:Actualización de ([a-z]+).+$'; 

Pls. note that to make it simpler the regex for filtering and text extraction is the same. I have fixed a bit yours and added a capturing group.