views:

210

answers:

4

I'm almost embarassed, but I'm struggling to create a regular expression to change something like cat dog mouse to a SQL where clause:

a.cat=b.cat AND a.dog=b.dog AND a.mouse=b.mouse

With

s/(\w*)\s?/a.$1=b.$1 AND /

I get

a.cat=b.cat AND a.dog=b.dog AND a.mouse=b.mouse AND a.=b. AND

Ouch. Help appreciated.

EDIT: I ended up using two consecutive regexes. Since I needed this in a SAS macro and I wanted my code to be concise, I wrote this macro:

%Macro rxchange(str,rx1,rx2,rx3,rx4,rx6,rx7,rx8,rx9,rx10);
    %Let rxno=1;
    %Do %While("&&rx&rxno" Ne "");
        %Let str=%SysFunc(PRXChange(%Str(&&rx&rxno), -1, %Str(&str)));        
        %Let rxno=%Eval(&rxno+1);
    %End;
    &str
%Mend;

/* Try this: */
%Put %rxchange(cat dog mouse,s/(\w+)\s?/a.$1=b.$1 /,s/(\s+)/ AND /);

Thanks for all who replied!

+3  A: 

Your first problem is you need + instead of *:

s/(\w+)\s?/a.$1=b.$1 AND /

This will solve the problem with a.=b..

Even then you will get an AND too much. You could solve this with a '1=1' at the end.

Are you sure you want to use regex here, and not a split, simple string manipulation, then a join? I think this would be simpler to understand.

Mark Byers
Thanks, I can split the manipulation into several parts. But from a learning point of view, it'd be nice to know if/how this could be done using a single regex
Ville Koskinen
Not in any nice way. The problem is that you have to replace three words, but only two gaps between words. You can do it if you use two separate regular expressions, one which transforms the words, and the other to transform the gaps between words. But that isn't a single regex, and it's less efficient than either of the other solutions I suggested.
Mark Byers
+2  A: 

You can split the line by spaces, perform s/^(.+)$/a.$1=b.$1/ (don't use regex for this simple situation though), then join the array with the "separator" "AND".

Make sure you trim the string before you start. The ending space is the reason for the extra a.=b..

KennyTM
+1 for the suggestion not to use regex: RegEx are powerful but often there are easier (and usually faster) ways to get to the target than a complex RegEx, like split() and join() in this case.
dbemerlin
+1  A: 

I can name that tune in 2 regular expressions!

str = prxchange('s/(\w+)/a.$1=b.$1/', -1, str);
str = prxchange('s/ +/ AND /', -1, str);
Greg Bacon
+1  A: 

You can do it with two regular expressions, one that matches everything but the first word, and one that matches the first.

C# code:

string where = "cat dog mouse";
where = Regex.Replace(where, @" (\w+)", " AND a.$1=b.$1");
where = Regex.Replace(where, @"^(\w+)", "a.$1=b.$1");
Guffa