R help - Aug 2012 - Regular expressions: stuck again...

Hi,

I'm currently reworking a report, originating from a MS Access database, but
should be implemented in R.
Now I'm facing the task to convert a lot of queries to postgreSQL.

What I want to do is make a function which takes the MS Access query as an
argument and returns the pgSQL version.
So:
SELECT [public_tblFiche].[Fichenr], [public_tblArtnr].[Artnr] FROM
[public_tblFiche], [public_tblArtnr] WHERE [public_tblFiche].[Artnr_ID]
[public_tblArtnr].[Artnr_ID];
or 
SELECT public_tblFiche.Fichenr, public_tblArtnr.Artnr FROM public_tblFiche,
public_tblArtnr WHERE public_tblFiche.Artnr_ID = public_tblArtnr.Artnr_ID;

Should become: 
SELECT \"public\".\"tblFiche\".\"Fichenr\",
\"public\".\"tblArtnr\".\"Artnr\" FROM
\"public\".\"tblFiche\",
\"public\".\"tblArtnr\" WHERE
\"public\".\"tblFiche\".\"Artnr_ID\"
\"public\".\"tblArtnr\".\"Artnr_ID\";

concrete:
The square backets  [ and ] should removed
and xxx_xxx.xxx  should become
\"xxx\".\"xxx\"\".\"xxx\"


When only queries with square brackets, I used 
gsub('[', '\"', x, fixed=TRUE), 
gsub(']', '\"', x, fixed=TRUE), 
gsub('_', '\"', x, fixed=TRUE), 

But to do the trick with regular expressions, I cant get a grip on this....

Anyone who can give me some help?


Thanks

Bart










--
View this message in context:
http://r.789695.n4.nabble.com/Regular-expressions-stuck-again-tp4641155.html
Sent from the R help mailing list archive at Nabble.com.

Hello,

try this:

x <- c("SELECT [public_tblFiche].[Fichenr],
[public_tblArtnr].[Artnr]", "SELECT public_tblFiche.Fichenr,
public_tblArtnr.Artnr")

# > The square backets [ and ] should removed
x <- gsub("[][]", "", x)

# > and xxx_xxx.xxx should become
\"xxx\".\"xxx\"\".\"xxx\"
x <- gsub("([[:alpha:]]+)_([[:alpha:]]+)\\.([[:alpha:]]+)",
"\"\\1\".\"\\2\".\"\\3\"", x)


-- 
Noia Raindrops
noia.raindrops at gmail.com