MySQL query – how to remove variable data

MySQL

I wonder if someone can help me please? I recently migrated from PHPBB forum software to MyBB software, but when the conversion finished I was left with lots of weird characters in many posts, such as <P>, </e>, etc.

I used the below query to remove things like this, and it has worked well and removed lots of these characters:

UPDATE mybb_posts SET message = REPLACE (message, '<P>', '') WHERE message LIKE '%<P>%';

My problem now is that the stuff I need to remove varies from post to post – here's a few examples:

">https://www.ebay.co.uk/itm/SUZUKI-DR-75 ... Sw7k9eIFHa</a><!-- m -->

">http://www.hagon-shocks.co.uk/catalog/c ... x?TypeID=F</a><!-- m -->
">https://www.moorespeedracing.co.uk/indi ... gKQjPD_BwE</a><!-- m -->

As you can see, each line that I need to remove starts and ends with the same group of characters.
I modified the above query using the % as the wildcard, so the query became:

UPDATE mybb_posts SET message = REPLACE (message, '">htt%m -->', '') WHERE message LIKE '%">htt%m -->%';

When I click "Simulate Query" I get a message saying 1643 rows affected, but when I click "Go" to run the query it comes back and tells me 0 rows affected.

Is there something wrong with my query? Can anyone suggest how to fix it or suggest another query to achieve the same outcome?
TIA

Best Answer

Thanks for your replies. The examples of text are shown in my original post, but I had to put the "code" formatting around them as the forum would not let me post without doing that. Here are the examples again:

">https://www.ebay.co.uk/itm/SUZUKI-DR-75 ... Sw7k9eIFHa</a><!-- m -->

">http://www.hagon-shocks.co.uk/catalog/c ... x?TypeID=F</a><!-- m -->
">https://www.moorespeedracing.co.uk/indi ... gKQjPD_BwE</a><!-- m -->

I want to remove this whole section of text, but as you can see the centre section (i.e. the www part) varies in each line. It is MySQL 5.5.65 Sorry, I have no idea how to use "fiddle".

Related Solutions

SQL Server to MySQL Migration – Remove UCS-2 Surrogate Pairs

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;

MySQL: check max chracter length (ERROR 1118 (42000): Row size too large)

According to this, your problem is not with VARCHAR fields. However:

SELECT id, LENGTH(field1)+LENGTH(field2)...+LENGTH(field_n) AS total_length WHERE total_length > 8126
Can be done using 2 queries for each field. (This can be done in a complex query if you want to use INFORMATION_SCHEMA):
- SELECT MAX(LENGTH(field1)) INTO @max_length1 FROM table_name;
- SELECT * FROM table_name WHERE LENGTH(field1)=@max_length1;

Note: Increasing innodb_page_size's value may be helpful in your case.

Edit

To generate all the queries with the help of information_schema, run the following queries:

CREATE TABLE queries(field_name varchar(255), query1 varchar(4000), query2 varchar(4000));

INSERT INTO queries SELECT column_name, 
CONCAT('SELECT MAX(LENGTH(',column_name,')) INTO @max_length FROM table_name;'), 
CONCAT('SELECT * FROM table_name WHERE LENGTH(',column_name,')=@max_length;')
FROM information_schema.columns WHERE table_schema='test' and table_name='myisam_table';

SELECT * FROM queries;

Note that you have to run query1 then query2 from the result. DO NOT run all query1 then all query2!

Best Answer

Related Solutions

SQL Server to MySQL Migration – Remove UCS-2 Surrogate Pairs

MySQL: check max chracter length (ERROR 1118 (42000): Row size too large)

Edit

Related Question