Return part of a string with variable length

Question

I have data in a table column like below:

host=0.0.0.0 port=5432 dbname=database_name user=pglogicaluser
host=0.0.0.0 port=5432 dbname=database_name2 user=pglogicaluser

I want to write a query to get the database names only like below:

database_name
database_name2

I could come up with something like:

select substring(column_name, '.+dbname=(.*)$') from table_name;

However, I couldn't figure how to stop the extraction before the user keyword starts.

• Sayad Xiarkakh: Yes all the data is from one column.
• Vérace: Is the database name UNIQUE?

Answer 1

you need to include a space after the regex group that acts as a delimiter to separate the user keyword from the text you want to match. To make the regex match in that case, an additional "any other character" is needed.

substring(column_name, '.+dbname=(.*) .*$') 

• Sayad Xiarkakh: Thank you that worked. I run the below regex and it worked too. SUBSTRING (column_name , '(?:dbname=)(.*?)(?:user)' )
• Vérace: @SayadXiarkakh - performance will be terrible!

Answer 2

If this string always has the form

host=x port=y dbname=name user=z

then there's a far better (i.e. faster) way of doing what you want. I did the following (all of the code below is available here):

CREATE TABLE str
(
 pg_str TEXT
);

Populate it:

INSERT INTO str VALUES
('host=0.0.0.0 port=5432 dbname=database_name user=pglogicaluser'),
('host=0.0.0.0 port=5432 dbname=database_name2 user=pglogicaluser');

First we use the SPLIT_PART() function. Its signature is SPLIT_PART(string, delimiter, position) (manual)

So, we run:

SELECT
 SPLIT_PART(pg_str, ' ', 3)
FROM str;

Result:

split_part
dbname=database_name
dbname=database_name2

Next, we apply the REPLACE() (same manual page) function to remove the dbname= part above:

SELECT
 REPLACE(SPLIT_PART(pg_str, ' ', 3), 'dbname=', '') AS dbname
FROM str;

Result:

dbname
database_name
database_name2

Now, what you could do is something like this:

ALTER TABLE str ADD COLUMN db TEXT GENERATED ALWAYS AS 
(REPLACE(SPLIT_PART(pg_str, ' ', 3), 'dbname=', '')) STORED;

so, now you don't have to perform complex queries on the fly - every time you INSERT or UPDATE your pg_connection_str, the db field will be modified accordingly. There is a space penalty to be paid, but it's relatively small.

As a general rule of thumb, if it can be done without regexes, then do it without regexes!