CODEWITHSUNDEEP
sqlpostgresql
user3262353
user3262353

Reputation: 107

Extract string before last occurence of a charcacter

My Specs:
Postgres 9.6.6, latest Ubuntu LTS

I need to exctract a part of a tring before the last occurence of a caracter:
exaple with a '.' 

'ab.cde.fghi' -> 'ab.cde'

at moment this is my code:

select 
 substr('ab.cde.fghi',1,length('ab.cde.fghi')-strpos(reverse('ab.cde.fghi'),'.'))

I 'm looking for a more funcional/elegant way to do that. Any suggestion is apreciated.

Thanks
Perez.

Upvotes: 2

Views: 75

Answers (2)

klin
klin

Reputation: 121524

Use a regular expression:

select substring('ab.cde.fghi' from '(.*)\.')

 substring 
-----------
 ab.cde
(1 row)

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use arrays:

SELECT c, sub.arr[array_upper(arr, 1)-1]  
FROM tab
,LATERAL (SELECT string_to_array(tab.c, '.'))sub(arr)

DBFiddle Demo

And if you don't like LATERAL just copy-paste:

SELECT c,
  (string_to_array(tab.c, '.'))[array_upper((string_to_array(tab.c, '.')), 1)-1]
FROM tab

Upvotes: 0

Related Questions