REPLACE syntax

[magicmarkuk]

Hello again

I have two questions regarding the REPLACE command.

The first query I am running is

[CODE]update phpbb_posts set post_text = replace(post_text, '[del]', '[ b][ i]')
update phpbb_posts set post_text = replace(post_text, '[/del]', '[/i][/b]')[/CODE]

Note - in the above code I have had to add a space before the b] and i] on line 1 as just doing it with a [ was marking the text bold and italic. I did try different ways of wrapping but couldn't get it to display properly.

and this gives a syntax error of

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update posts set post_text = replace(post_text, '[/del]', '')' at line 2

Now from previous posts I am sure that this is beacause of the / character but I have tried // i.e.

update phpbb_posts set post_text = replace(post_text, '[//del]', '[//i][/b]')

but this gives the same error message.

I was hoping this was the easier query!

The second question is:

I want to run a similar replace query to replace:

[quote author="magicmarkuk" date="1315007444"]

with simply

[quote]

from date onwards will always be 17 characters but of course the author name can be different.

Any help as ever really appreciated.

Thanks
Mark

[magicmarkuk]

Quick update - I just realised I had missed a ; off the end of the lines and the query worked with no syntax error so the first question is answered.

Mark

[smantscheff]

MySQL does not natively support regular expression replacements, therefore there is no built-in functionality for your problem.
You could either compile MySQL with regex support (there is an experimental plug-in somewhere on the net), or (more easily) write a function in MySQL which does the replacement.
The function would look like

CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end;

Then use this function like in

update mytable set posts_texts = cutQuote(posts_texts);

[magicmarkuk]

Thank you for this

I have used the following code:

CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end;

update phpbb_posts set posts_text = cutQuote(posts_text);

but get the following error message:

Error

SQL query:

CREATEFUNCTION cutQuote(

t text
) RETURNS text DETERMINISTIC BEGIN declare i1 integer;

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

Any further help really appreciated.

Regards
Mark

[smantscheff]

When you code stored procedures you have to alter the standard delimiter. The standard delimiter is the semicolon which separates statements. You can change it with the "delimiter" statement, and then the code looks like:

delimiter //
CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end //
delimiter ;

And don't use update before you have tested with a select that you get the desired results.

[magicmarkuk]

I have tried the following code:

delimiter //
CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end //
delimiter ;

update phpbb_posts set post_text = cutQuote(post_text);

but this is giving the error message:

Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 11
STR: //
SQL: delimiter //
CREATE FUNCTION cutQuote(t text) RETURNS text
DETERMINISTIC
begin
declare i1 integer;
set i1 = locate( '[QUOTE ', t );
while (i1 > 0) do
set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
set i1 = locate( '[QUOTE ', t );
end while;
return t;
end //

SQL query:

delimiter // CREATE FUNCTION cutQuote(t text) RETURNS text DETERMINISTIC begin declare i1 integer; set i1 = locate( '[QUOTE ', t ); while (i1 > 0) do set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17)); set i1 = locate( '[QUOTE ', t ); end while; return t; end //

MySQL said:
#1304 - FUNCTION cutQuote already exists

The last element may relate to the fact that I ran the query again to get the message - in the original - no rows were updated which would indicate that it isn't finding anything.

Sorry for having to post again for help but I am out of my depth.

Thanks
Mark

[smantscheff]

You seem to use an interface which ####s up the original line breaks (maybe phpMyAdmin?). Use the mysql command line client or HeidiSQL or Navicat instead.
Also, since you already created a function now, you will have to prepend your code with

DROP FUNCTION IF EXISTS cutQuote;

[magicmarkuk]

Yes - using phpMyAdmin.

I have now installed HeidiSQL and running

DROP FUNCTION IF EXISTS cutQuote;
delimiter //
CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end //
delimiter ;

update phpbb_posts set post_text = cutQuote(post_text);

I get the following:

DROP FUNCTION IF EXISTS cutQuote;
CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end ;
update phpbb_posts set post_text = cutQuote(post_text);
/* 0 rows affected, 0 rows found. Duration for 3 queries: 5.343 sec. */

So it would appear no syntax errors but likewise no results.

The table is phpbb_posts and column is post_text

Sorry for being a pain

Mark

[smantscheff]

Well, you should have at least a dim picture of what you are doing before you trust a total stranger to run code in your database. So don't run this code:

drop table phpbb_posts;

but instead

select posts_text, cutQuote(posts_text) from phpbb_posts

to see if the function works properly. And if it does, use the function in an update clause.

[magicmarkuk]

Point taken!

I have rerun using the HeidiSQL and now in the morning it all seems clearer.

The select is bringing back all the post_text together with a cutQuote(post_text). However, both appear the same.

For example

post_text

[quote author="Fox" date="1266787165"]Sorry, did I say five?  Meant three.  Common mistake among CIPFA students[/quote]

There are three types of accountant. Those who can count and those who can't ;o)

and cutQuote(post_text)

[quote author="Fox" date="1266787165"]Sorry, did I say five?  Meant three.  Common mistake among CIPFA students

There are three types of accountant. Those who can count and those who can't ;o)

Now one reason it may not be working is that the code actually in the table is different to that which I originally quoted (no pun intended) as I was taking it from the forum side rather than the backend - my mistake.

I have tried working through the function but cannot quite see what it is doing. For example I can see that

set i1 = locate( '[QUOTE ', t );

is finding the first instance of quote

While there is an instance it is using concat to pull together the new value - and this is where I am struggling to understand how it works.

Mark

[magicmarkuk]

Update - I have realised why the values were different - bacause has run another routine via phpbb which inserted additional values.

I have reloaded the database prior to this and rerun the cutQuote query but am still seeing this:

post_text

[quote author="caz128" date="1202738203"]I'm exstatically enthusiastic about it.:coolgrin: 

I'm sure that once those people who aren't so keen get used to the changes they'll come around 

The only thing I have missed so far is the little box that used to pop open and tell me I'd got a new private message. Are there any intentions/possibilities of adding that back?[/quote]

Added to the possibilities list. During the design stage it was added temporarily in the top right hand corner but for some reason didn't sit comfortably. Will revisit.

and as cutQuote(post_text)

[quote author="caz128" date="1202738203"]I'm exstatically enthusiastic about it.:coolgrin: 

I'm sure that once those people who aren't so keen get used to the changes they'll come around 

The only thing I have missed so far is the little box that used to pop open and tell me I'd got a new private message. Are there any intentions/possibilities of adding that back?[/quote]

Added to the possibilities list. During the design stage it was added temporarily in the top right hand corner but for some reason didn't sit comfortably. Will revisit.

Mark

[smantscheff]

1) The lcoate function is case-sensitive. It won't find quote but QUOTE.
2) I made a mistake in the length of the first chunk which is cut from the text (bad cut and paste habits).
3) You mis-stated the length of the string to be cut.
Working sample:

drop function if exists cutQuote;
delimiter //
CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[quote ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 - 1 ), substr( t, i1 + 41));
                set i1 = locate( '[quote ', t );
        end while;
        return t;
end //
delimiter ;

select cutQuote('[quote author="caz128" date="1202738203"]I\'m exstatically enthusiastic about it.:coolgrin: 

I\'m sure that once those people who aren\'t so keen get used to the changes they\'ll come around 

The only thing I have missed so far is the little box that used to pop open and tell me I\'d got a new private message. Are there any intentions/possibilities of adding that back?[/quote]

Added to the possibilities list. During the design stage it was added temporarily in the top right hand corner but for some reason didn\'t sit comfortably. Will revisit.');

Result:

I'm exstatically enthusiastic about it.:coolgrin: 

I'm sure that once those people who aren't so keen get used to the changes they'll come around 

The only thing I have missed so far is the little box that used to pop open and tell me I'd got a new private message. Are there any intentions/possibilities of adding that back?[/quote]

Added to the possibilities list. During the design stage it was added temporarily in the top right hand corner but for some reason didn't sit comfortably. Will revisit.

But can you rely on the author attribute string being 6 chars long? If not, the function has to search for the first closing bracket ] after i1 and cut between those positions.

[magicmarkuk]

Thank you - I will try this.

You are right in the ascertain that the author attribute will not always be 6 characters in length so it would be preferable to find the first /]

Regards
Mark

[smantscheff]

That's why the variable is called i1. With i2 the function reads:

CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
	declare i2 integer;
        set i1 = locate( '[quote ', t );
        while (i1 > 0) do
                set i2 = locate( ']', t, i1 );
                set t = concat( substr( t, 1, i1 - 1 ), substr( t, i2 + 1));
                set i1 = locate( '[quote ', t );
        end while;
        return t;
end //

[magicmarkuk]

Thank you ever do much for your kind help and patience.

It is working perfectly.

Thanks again

Mark

[smantscheff]

The last version of the function is in fact only a working prototype. Fore use in a production environment there should be precautions for i2 being NULL, the place markers should be parameters instead of literals (with guards against empty parameters) and there should be an optional parameter for case sensivity. But for quick and dirty admin work it's fine.

[magicmarkuk]

Well the routine worked perfectly but now I seem to have an issue when I am exporting the database from XAMPP and trying to use bigdump to import it into my live server. I get the error message:

Error at the line 39: end$$

Query: --
--
--
--
CREATE DEFINER=`root`@`localhost` FUNCTION `cutQuote`(t text) RETURNS text CHARSET utf8 COLLATE utf8_bin
DETERMINISTIC
begin
declare i1 integer;
declare i2 integer;
set i1 = locate( '[quote ', t );
while (i1 > 0) do
set i2 = locate( ']', t, i1 );
set t = concat( substr( t, 1, i1 - 1 ), '

', substr( t, i2 + 1));
set i1 = locate( '[quote ', t );
end while;
return t;
end

MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE utf8_bin DETERMINISTIC begin declare i1 integer; declare i2' at line 5

Now if I test it on XAMPP and get the error running the following code seems to clear the way:

drop function if exists cutQuote;

Any idea how I can either correct the export so that it will not hit an error with bigdump or amend the sql file that I am trying to import to get over the problem.

Would dropping the function before exporting work?

Thanks
Mark

[smantscheff]

Use the comnand line utilities mysqldump and mysql to dump your database and feed it into another server.