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