CODEWITHSUNDEEP
sqlpostgresql
Elizama Melo
Elizama Melo

Reputation: 153

Extract first word to first space - POSTGRES

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.

Upvotes: 8

Views: 3084

Answers (2)

Jim Jones
Jim Jones

Reputation: 19613

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

Upvotes: 8

Stefanov.sm
Stefanov.sm

Reputation: 13029

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.

Upvotes: 0

Related Questions