How to extract last part of a string using regex

Question

I want to use part of a string in a where statement, the issue is to be able to correctly extract the ending part of the string.

example of the string is below

Token=hdhddjdjdjjkfdjkfkjdfu743895390458059035ufjfjfjfdjkdsur0934r9rjdjdsjsdkk&value=CRT&SaveAndContinue=

I want to get "SaveAndContinue" and for the example below, I want to return "SaveAndContinue"

Token=hdhdhdb.sjcdchwuhv;ovhvjvdbkdsjskdsj372893984024fhewjsac01239301913ru32rcbjs&email=ShshtwuidS%hdhdhdhsjkweuydjdj.COM&confirmation=true&SaveAndContinue=

Stefanov.sm · Answer 1 · 2023-08-23T13:47:05.940

You may use substring function and this regexp &([^&=]+)=$ i.e. pick characters except '&' and '=' after the last '&' until the '=' before the end of string.

select substring('hdhddjdjdjjkfdjkfkjdfu743895390458059035ufjfjfjfdjkdsur0934r9rjdjdsjsdkk&value=CRT&SaveAndContinue='
                 from '&([^$=]+)=$');
select substring('hdhdhdb.sjcdchwuhv;ovhvjvdbkdsjskdsj372893984024fhewjsac01239301913ru32rcbjs&email=ShshtwuidS%hdhdhdhsjkweuydjdj.COM&confirmation=true&SaveAndContinue='
                 from '&([^&=]+)=$');

Both return 'SaveAndContinue'.

DB-fiddle

nbk · Answer 2 · 2023-08-23T14:00:50.963

0

You could split it, which works with Postgres 14 and higher, but all string function cost time

CREATE TABLE my_table ( a text)

CREATE TABLE

INSERT INTO my_table VALUES ('Token=hdhddjdjdjjkfdjkfkjdfu743895390458059035ufjfjfjfdjkdsur0934r9rjdjdsjsdkk&value=CRT&SaveAndContinue='),
('Token=hdhdhdb.sjcdchwuhv;ovhvjvdbkdsjskdsj372893984024fhewjsac01239301913ru32rcbjs&email=ShshtwuidS%hdhdhdhsjkweuydjdj.COM&confirmation=true&SaveAndContinue=')

INSERT 0 2

SELECT  trim(trailing '=' from split_part(a, '&', -1))
  FROM my_table;

rtrim
SaveAndContinue
SaveAndContinue

SELECT 2

fiddle

edited Aug 23 '23 at 14:00

answered Aug 23 '23 at 13:42

nbk

45,398
8
30
47

Hi @nbk, thanks for your help. I tried and getting an error "Index must be greater than zero" – icenature Aug 23 '23 at 13:58
which postgres version are you using? – nbk Aug 23 '23 at 13:59
this works for Postgres 14 and higher – nbk Aug 23 '23 at 14:00
I tagged the wrong sql, I'm using presto sql – icenature Aug 23 '23 at 14:12

score 0 · Accepted Answer · answered Aug 23 '23 at 14:33

The task is not well defined, but assuming that you want to get the data between last & and = (both should be present) then you can try regexp_extract:

-- sample data
WITH dataset (str) AS (
    values ('Token=hdhddjdjdjjkfdjkfkjdfu743895390458059035ufjfjfjfdjkdsur0934r9rjdjdsjsdkk&value=CRT&SaveAndContinue=')
)

-- query
select regexp_extract(str, '&([^$=]+)=[^=]*$', 1)
from dataset;

Output:

     _col0      
-----------------
 SaveAndContinue

How to extract last part of a string using regex

3 Answers3