Replacing different substrings without clear pattern in python
<p>I need to replace part of some queries (strings) which <strong>don't</strong> always have the same substring to replace. </p>

<pre><code>query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value 
from table 
where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00' 
group by YEAR(utimestamp), MONTH(utimestamp), id """
</code></pre>

<p>I want to replace the part regarding date after <em>group by</em>. </p>

<p>This part could be any of the following strings:</p>

<pre><code>'YEAR(utimestamp), MONTH(utimestamp), DAY(utimestamp),'
'YEAR(utimestamp), MONTH(utimestamp), WEEK(utimestamp),'
'YEAR(utimestamp), MONTH(utimestamp),'
'YEAR(utimestamp),'
</code></pre>

<p>My idea is to search for "<em>(utimestamp),</em>" and get the part from the left (YEAR, DAY, WEEK or MONTH) searching for the first blank space in the left. After having those removed I want to insert another substring, but how can I insert this substring now that I have blank spaces where the new substring should go.</p>

<p>I thought of getting the index everytime I removed a string and once there's no more to remove insert the substring there but I think I'm complicating things.</p>

<p>Is there an easier, neat way of doing this? Am I missing something?</p>

<p><strong>EXAMPLE:</strong></p>

<p>Input string that needs replacement:</p>

<p>query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value 
    from table 
    where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00' 
    group by YEAR(utimestamp), MONTH(utimestamp), id """</p>

<p>or </p>

<pre><code>query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value 
        from table 
        where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00' 
        group by YEAR(utimestamp), id """
</code></pre>

<p>or</p>

<pre><code>query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value 
        from table 
        where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00' 
        group by YEAR(utimestamp), MONTH(utimestamp), WEEK(utimestamp), id """
</code></pre>

<p>etc.</p>

<p>Desired result:</p>

<pre><code>query_replaced = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value 
    from table 
    where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00' 
    group by MY_COOL_STRING, id """
</code></pre>

<p>If should work for all those cases (and more, the ones stated before)</p>

<p>Following @Efferalgan answer I came up with this:</p>

<pre><code>query_1 = query.split("group by")[0]
utimestamp_list = query.split("(utimestamp)")
l = len(utimestamp_list)
query_2 = utimestamp_list[l-1]
query_3 = query_1 + " group by MY_COOL_STRING" + query_2
</code></pre>

<p>You may use <a href="https://docs.python.org/2/library/re.html#re.sub" rel="nofollow"><code>re.sub()</code></a> of regex to achieve it:</p>

<pre><code>&gt;&gt;&gt; import re
&gt;&gt;&gt; replace_with = 'HELLO'
&gt;&gt;&gt; new_string  = re.sub('group by\s\w+\(utimestamp\)', "group_by"+replace_with, query)

# Value of new_string: SELECT  as utimestamp, sum(value) as value 
# from table 
# where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00' 
# group by HELLO, HELLO, id
</code></pre>

<p>where <code>replace_with</code> is the content you need to update with the pattern <code>'\w+\(utimestamp\)'</code> and <code>query</code> is the string you mentioned in the code.</p>

<p>Here, <code>\w+</code> means alphabets with occurence of one or more, whereas <code>\(utimestamp\)</code> along with that denotes, words followed by the string <code>(utimestamp)</code>.</p>

<p><strong>EDIT</strong>:</p>

<p>As is mentioned in the comment, to replace all instances of the <code>timestamp</code> in the <code>query</code>, regex expression should be like:</p>

<pre><code>re.sub('group by\s\w+\(utimestamp\)(,\s*\w+\(utimestamp\))*', "group_by" + replace_with, query)

# Returned Value:  
# SELECT DATE(utimestamp) as utimestamp, sum(value) as value from table
# where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00'
# group by HELLO, id
</code></pre>

python string replace