Regex to convert a space separated list to a SQL where clause

Question

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!

Answer 1

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.