How to use regexp_substr in Redshift

Question

I tried following query. to detect login name as follows.

select regexp_substr('{id:ABCDE,priority:Similar,logins:[test123]}','logins:$$(.*)$$') as login_name

My desired result is to get test123. But when I execute this query, it returned nothing.

What is the wrong point of that ? If someone has opinion,please let me know

Thanks

user_6759368 · Answer

You can use regexp_replace function and get capture group with \:numberGroup

select regexp_replace(
  '{id:ABCDE,priority:Similar,logins:[test123]}',
  '.*logins:\s*$$(.*)$$.*', 
  '\1'
) as login_name