regex needed to remove parts of SQL

jeanluca has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

I'm facing the following problem: I need to convert the SQL query

SELECT f1 AS x1,f2,f3,f4 as x2, (f4= f5) as x3 FROM %t"
[download]

into

SELECT x1, f2, f3, x2, x3 FROM ( SELECT f1 AS x1,f2,f3,f4 as x2, (f4= 
+f5) as x3 FROM Some_table_2006) UNION (SELECT f1 AS x1,f2,f3,f4 as x2
+, (f4= f5) as x3 FROM Some_tabel_2007)) As Tbl1 ;
[download]

I need to make 1 big query because I don't want to merge the result of N tables together myself(the WHERE part can get complicated)
So my problem is now to convert

SELECT f1 AS x1,f2,f3,f4 as x2, (f4= f5) as x3 FROM
[download]

into

SELECT x1,f2,f3,x2, x3 FROM
[download]

To simplify the problem I started with

SELECT f1,f2,f3,f4 as x2, (f4= f5) as x3 FROM
[download]

It turns out that even this exercise is too hard for me. This is what I have so far

use strict;
use warnings;

my $s = "SELECT f1,f2,f3,f4 as x2, (f4= f5) as x3 FROM ...." ;

$s =~ s/,\s*.*?\s+as/,/gi ;

print "OUT: $s\n";
[download]

Gives

OUT: SELECT f1, x2, x3 FROM ....
[download]

All suggestions are welcome! or if there are better ways to do this I'm very interested!!

Thnx a lot
LuCa

Comment on regex needed to remove parts of SQL Select or Download Code

Replies are listed 'Best First'.
Re: regex needed to remove parts of SQL by mayaTheCat (Scribe) on Sep 12, 2007 at 10:25 UTC
What about the following? `$sql =~ s/(select\|,)\s[^,]?\s+as/$1/gi ;` [download] Oguz --------------------------------- life is ... `$mutation = sub { $_[0] =~ s/(.)/rand()<0.1?1-$1:$1/ge };`	[reply] [d/l] [select]
Re^2: regex needed to remove parts of SQL by jeanluca (Deacon) on Sep 12, 2007 at 10:40 UTC
great!!, thats what I need. One last thing; what if I don't want to use $1 ? Should I split it in 2 expressions `$sql =~ s/(?:SELECT)\s[^,]?\s+as/SELECT/gi ; $sql =~ s/,\s[^,]?\s+as/,/gi ;` [download] This works, except I don't understan why (?: .... ) is not working (I don't want to replace the word SELECT) LuCa	[reply] [d/l]
Re^3: regex needed to remove parts of SQL by mayaTheCat (Scribe) on Sep 12, 2007 at 11:15 UTC
In that case, `$sql =~ s/(?<=select)\s[^,]?\s+as//gi ; $sql =~ s/(?<=,)\s[^,]?\s+as//gi ;` [download] Looks like the problem was that (?:) is non-capturing grouping. It stills eats up the characters. What you need is lookbehind (?<=). I also tried `$sql =~ s/(?<=select\|,)\s[^,]?\s+as//gi ;` [download] But the interpreter refused and said `Variable length lookbehind not implemented in regex` [download] So, it looks like you have to have two regexes if you do not want to use $1. Oguz --------------------------------- life is ... `$mutation = sub { $_[0] =~ s/(.)/rand()<0.1?1-$1:$1/ge };`	[reply] [d/l] [select]
Re^4: regex needed to remove parts of SQL by jeanluca (Deacon) on Sep 12, 2007 at 12:08 UTC
Re: regex needed to remove parts of SQL by GrandFather (Saint) on Sep 12, 2007 at 10:50 UTC
If you need to pull out the list of selected columns then the following may help: `use strict; use warnings; my $sel = 'SELECT f1 AS x1,f2,f3,f4 as x2, (f4= f5) as x3 FROM'; $sel =~ /SELECT\s+(.?)FROM/i or die "Select clause not found in: $sel +"; my @parts = map {/(\w+)\s$/; $1} split /\s,\s/, $1; print "SELECT ", join (', ', @parts), " FROM\n";` [download] Prints: `SELECT x1, f2, f3, x2, x3 FROM` [download] DWIM is Perl's answer to Gödel	[reply] [d/l] [select]
Re: regex needed to remove parts of SQL by Anonymous Monk on Sep 12, 2007 at 15:10 UTC
if you just need to delete `'something-or-other as'`, something like this may do the trick: `C:\@Work\Perl>perl -wMstrict -e "$_ = shift; my $item = qr{ \w+ \s+ }xms; my $group = qr{ $ [^)]* $ \s* }xms; s{ (?: $item \| $group) as }{}xmsgi; print" "SELECT f1 as x1,f2,f3,f4 as x2, (f4= f5) as x3 FROM ...." SELECT x1,f2,f3, x2, x3 FROM ....` [download] if other things may predece `'as'`, they can be defined and added to the grouped alternation.	[reply] [d/l] [select]