How to find a string that varies in position

2

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 does not always come same position, so it does not do to simply do substring (...). I think you should 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 eh:

select (....)

cost = 399301

----------------- sample string 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 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)"
    
asked by anonymous 14.01.2017 / 20:58

1 answer

1

I could almost solve the problem with regular expression. Here's 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 appeared initially, and I would like to remove it. Anyone have any tips on how to remove the starting point? What do I need eh of the number after the two consecutive points ".."

    
15.01.2017 / 02:37