return specific content with regex - POSTGRESQL

Asked

Viewed 62 times

0

I have the query below, where are captured the 3 largest memory consuming processes on the machine. I would like my select to display only the pid and the name of the process contained in {name: }

SELECT NORMAL

top_mem   
-----------------------------------------------------------------------
12555, {memory_percent: 3.6073870265949464, name: code},10581, {memory_percent: 5.82421985505014, name: VirtualBoxVM},11518, {memory_percent: 13.986433224706513, name: java}

SELECT DESIRED (MATRIX)

12555, code
10581, VirtualBoxVM
11518, java

QUERY

SELECT top_mem FROM tbl_top_consum_hosts WHERE (top_mem regexp '^name[a-z]')
database postgresql regex
  • 1

    hey hello! in the normal select has only one row with all records? could show the table structure ?

  • 1

    I find it easier to handle this outside of SQL, any language has functions that handle strings more easily (and perhaps without needing regex, since some splits should solve...)

  • Thanks for the tip, I will test in python!

1 answer

1


A possibility:

SELECT regexp_matches(regexp_split_to_table('12555, {memory_percent: 3.6073870265949464, name: code},10581, {memory_percent: 5.82421985505014, name: VirtualBoxVM},11518, {memory_percent: 13.986433224706513, name: java}', '},*'), '([[:digit:]]{5}).*name: ([[:alpha:]]+$)', 'g');

Maybe you can simplify with regexp_matches.

Browser other questions tagged database postgresql regex

You are not signed in. Login or sign up in order to post.