homepage Welcome to WebmasterWorld Guest from 54.167.41.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySql escape regex char
getting repetition-operator operand invalid error
Demaestro




msg:4324292
 9:34 pm on Jun 9, 2011 (gmt 0)

Hello,

I am working in a joomla site that uses jumi scripts.

Using the built in site search some articles are being returned because the search term matches something in the Jumi tag and returns the article when it shouldn't be.

I am trying to do a regex replace in MySql, I created a function and it works fine however it doesn't like some of my regex strings.

I get the message:
"repetition-operator operand invalid"

I looked up the error and found a bunch of stuff talking about how MySQL escapes chars and that it treats * and + differently. One fix I saw said to use double backslash but that isn't working.

Does anyone know how to escape this so that MySql's REGEXP will like this?

\{jumi.*?/jumi}

I have tried

\{jumi.\\*?/jumi}

But that doesn't work, I know it is the * because if I remove it I don't get the error but I don't get my match either.

select id, introtext from jos_content where introtext REGEXP "\{jumi.*?/jumi}"
^^^^^^^^^^^^^^^^^^^^^^^^^^^
This will throw the error

I am not too familiar with MySql so any insight would be very welcome.

Thanks in advance.

 

Demaestro




msg:4324302
 9:50 pm on Jun 9, 2011 (gmt 0)

well without the ? works

\{jumi.*/jumi}

However now I am seeing my function isn't do what I thought it would.

Does anyone see anything missing?

************************************
Params:
pattern text,
replacement text,
original text

BEGIN
declare temp text;
declare ch varchar(1);
declare i int;

set i = 1;
if original REGEXP pattern then
loop_label: LOOP
if i>char_length(original) OR not original REGEXP pattern then
LEAVE loop_label;
end if;
set ch = substring(original,i,1);
if ch REGEXP pattern then
set original = replace(original,ch,replacement);
end if;
set i=i+1;
END LOOP;
end if;
return original;

END
*************************************

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved