How to search for a string that varies from position

Asked

Viewed 340 times

1

See below two results returned from a table where I store sqls query execution plan. I use POSTGRESQL I need to capture only the string cost=399301.55 of the result below. The word cost always comes at the beginning, but it doesn’t always come at same position, so it is not possible to simply substring (...). I think one must find the initial and final position every time, to be able to extract only the cost value. If anyone can help me, the expected result for the example below is:

select (.... )

cost=399301

----------------- example string -----------------------

"Sort  (cost=399301.55..399301.57 rows=6 width=36)"
"  Sort Key: l_returnflag, l_linestatus"
"  ->  HashAggregate  (cost=399301.21..399301.48 rows=6 width=36)"
"        ->  Seq Scan on h_lineitem  (cost=0.00..250095.98 rows=5968209 width=36)"
"              Filter: (l_shipdate <= (to_date('1998/12/01'::text, 'YYYY/MM/DD'::text) - '10 days'::interval day))"

---------------- another example string --------------------

"Aggregate  (cost=7922058.70..7922058.71 rows=1 width=16)"
"  ->  Hash Join  (cost=1899763.92..7922058.69 rows=1 width=16)"
"        Hash Cond: (h_lineitem.l_partkey = h_part.p_partkey)"
"        Join Filter: (((h_part.p_brand = 'Brand#13'::bpchar) AND (h_part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (h_lineitem.l_quantity >= 4::double precision) AND (h_lineitem.l_quantity <= 14::double precision) AND (h_ (...)"
"        ->  Seq Scan on h_lineitem  (cost=0.00..235156.84 rows=211094 width=32)"
"              Filter: ((l_shipmode = ANY ('{AIR,"AIR REG"}'::bpchar[])) AND (l_shipinstruct = 'DELIVER IN PERSON'::bpchar))"
"        ->  Hash  (cost=1183158.46..1183158.46 rows=35278997 width=33)"
"              ->  Seq Scan on h_part  (cost=0.00..1183158.46 rows=35278997 width=33)"
"                    Filter: (p_size >= 1)"
sql postgresql

  • Use combinations of functions such as POSITION and SUBSTRING, I would think of a user function to treat this , the second step and review , if possible, the https://www.postgresql.org/docs/9.1/static/functions-string.ht ml

  • Forget the template comment ....

  • http://www.postgresonline.com/journal/archives/171-Explain-Plans-PostgreSQL-9.0-Text,-JSON,-XML,-YAML-Part-1-You-Choose.html

  • Ola Mota, I was thinking of doing a function even first by taking the cost= position and the upper limit. And after delimiting the start and end take the value using substring ( from for) .. SELECT POSITION ('.. ' IN 'Aggregate (cost=27726324.40.. 27726324.41 Rows = '); SELECT POSITION ('Rows' IN 'Aggregate.. But it occurred to me to use regular expression, this almost right, I’m only getting the value before the .. (two points) and actually wanted to take the value after the .. (two points) see... select substring('Aggregate (cost=27726324.40.. 27726324.41 Rows =' from '[0-9]+.? [0-9]{2}') ;

  • I don’t know Postgreesql but does this table or other does not already have separate data ?

  • In Oracle I know you have a table called Plantable that has information divided by field, but in postgresql I know an extension that does this pg_stats_plan, but I can not use it for project and version reasons. Unfortunately I have to get that way.

Show 1 more comment

1 answer

0

I almost managed to solve the problem with Regular Expression. See below what I did: 

select substring('Aggregate (cost=27726324.40..27726324.41 Rows' from '[.][0-9]+.?[0-9]{2}');

-------- RESULT . 27726324.41

Notice that a point has appeared initially, and I would like to remove it. Does anyone have any tips on how to remove the starting point ? What I need is the number after the two consecutive points ".."

  • I was able to modify the ER and was able to remove the starting point (.27726324.41), with the following solution: select substring('Aggregate (cost=27726324.40.. 27726324.41 Rows' from '[. ]Y*([0-9]+.? [0-9]{2})') RESULT 27726324.41

Browser other questions tagged sql postgresql

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