Return part of a string with variable length

The question:

I have data in a table column like below:

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

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


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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 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=(.*) .*$') 

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment