Change the values of string in parenthesis which starts with Varchar

Question

I have a python variable (type unicode), which holds a query statement:

CREATE TABLE afg_temp (tadm1_code INTEGER NOT NULL, tadm1_name VARCHAR(10) NOT NULL, test VARCHAR(4));

I need to replace all the values in each VARCHAR occurence with 254. Initially I thought it would be enough to use the replace() function but this won't work as the numbers in the parenthesis are random.

Is there a way to do this using regex?

So, have you tried anything yet? Looks like a straight-forward task. [Matching parentheses with a regex](http://stackoverflow.com/questions/5302942/matching-parentheses-in-python-regular-expression) is a solved issue. — Wiktor Stribiżew, Sep 14 '16 at 12:06

score 4 · Accepted Answer · answered Sep 14 '16 at 12:07

4

with regexp:

import re
query = 'CREATE TABLE afg_temp (tadm1_code INTEGER NOT NULL, tadm1_name VARCHAR(10) NOT NULL, test VARCHAR(4));'
print re.sub(r'VARCHAR\([0-9]*\)','VARCHAR(254)',query)

answered Sep 14 '16 at 12:07

Laurens Koppenol

2,946
2
20
33

score 1 · Answer 2 · answered Sep 14 '16 at 12:09

1

import re

s='''CREATE TABLE afg_temp (tadm1_code INTEGER NOT NULL, tadm1_name VARCHAR(10) NOT NULL, test VARCHAR(4));'''
re.sub('VARCHAR\([0-9]{1,3}\)','VARCHAR(254)',s)

There are plenty of other solutions and you don't even need python for this. The easiest would be to use sed.

answered Sep 14 '16 at 12:09

e4c5

52,766
11
101
134

what if you have VARCHAR(8000)? ;) – Laurens Koppenol Sep 14 '16 at 12:11
1

good point I see that you got ahead of me by a few seconds with an answer that uses * . I will not edit my answer to avoid detracting the value of your answer. +1 from me – e4c5 Sep 14 '16 at 12:15

Change the values of string in parenthesis which starts with Varchar

2 Answers2