Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Modify Query from MS SQL to MYSQL

I want to convert this update to work on mysql:
update posts set post = '[media]' + SUBSTRING(post, charindex('=', post) + 1, charindex(']', post, 2) - charindex('=', post) - 1) + '[/media]'

Open in new window

0
Nathan Riley
Asked:
Nathan Riley
  • 9
  • 8
1 Solution
 
SharathData EngineerCommented:
try this.
UPDATE posts 
   SET post = '[media]' + SUBSTRING(post,LOCATE('=',post) + 1,LOCATE(']',post,2) - LOCATE('=',post) - 1) + '[/media]'

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
that returned all 0's
0
 
SharathData EngineerCommented:
Can you post some sample value for post column before and after update on MSSQL?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Nathan RileyFounder/CTOAuthor Commented:
before

[url=http://domain.com/blah.flv]blah video[/url]
[url=http://domain.com/video/name.mp4]name of him[/url]
[url=http://domain.com/video/2001/funny.flv]funny clip[/url]

Open in new window


after
[media]http://domain.com/blah.flv[/media]
[media]http://domain.com/video/name.mp4[/media]
[media]http://domain.com/video/2001/funny.flv[/media]

Open in new window

0
 
SharathData EngineerCommented:
Try this query.
UPDATE Posts 
   SET Post = CONCAT('[media]',SUBSTRING(post,LOCATE('http',post),LOCATE(']',post)), 
                     '[/media]');

Open in new window


Tested on your sample data.
SELECT * FROM Posts;
+--------------------------------------------------------------+
| post                                                         |
+--------------------------------------------------------------+
| [url=http://domain.com/blah.flv]blah video[/url]             |
| [url=http://domain.com/video/name.mp4]name of him[/url]      |
| [url=http://domain.com/video/2001/funny.flv]funny clip[/url] |
+--------------------------------------------------------------+
3 rows in set (0.00 sec)

UPDATE Posts 
   SET Post = CONCAT('[media]',SUBSTRING(post,LOCATE('http',post),LOCATE(']',post)), 
                     '[/media]');
 
SELECT * FROM Posts; 
+-------------------------------------------------------------+
| post                                                        |
+-------------------------------------------------------------+
| [media]http://domain.com/blah.flv]blah [/media]             |
| [media]http://domain.com/video/name.mp4]name [/media]       |
| [media]http://domain.com/video/2001/funny.flv]funny[/media] |
+-------------------------------------------------------------+
3 rows in set (0.00 sec)

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
that's close, but the result set is wrong

you have:
[media]http://domain.com/blah.flv]blah [/media]             
[media]http://domain.com/video/name.mp4]name [/media]       
[media]http://domain.com/video/2001/funny.flv]funny[/media]

Open in new window


I need:
[media]http://domain.com/blah.flv[/media]
[media]http://domain.com/video/name.mp4[/media]
[media]http://domain.com/video/2001/funny.flv[/media]

Open in new window

0
 
SharathData EngineerCommented:
check this.
UPDATE Posts 
   SET Post = CONCAT('[media]',SUBSTRING(post,LOCATE('http',post),LOCATE(']',post) - LOCATE('http',post)),
                     '[/media]');

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
returns
[media][/media]

Open in new window

0
 
SharathData EngineerCommented:
Because you already ran my first UPDATE statement. Run my last update statement only.
0
 
SharathData EngineerCommented:
If possible, truncate your table and re-insert the records, then run my last update statement.
0
 
Nathan RileyFounder/CTOAuthor Commented:
I did that, same result when I do select * from posts I see the post field has data in it.  When I run your query it only shows
[media][/media]

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
I didn't run the update I ran the select b/c I don't want to update until I get the results I want.

select CONCAT('[media]',SUBSTRING(post,LOCATE('http',post),LOCATE(']',post) - LOCATE('http',post)),
                     '[/media]') from posts;

Open in new window



result was
[media][/media]

Open in new window

0
 
SharathData EngineerCommented:
I ran the UPDATE statement and got the correct result.
mysql> truncate table Posts;
Query OK, 3 rows affected (0.06 sec)

mysql> insert into Posts values ('[url=http://domain.com/blah.flv]blah video[/url]');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Posts values ('[url=http://domain.com/video/name.mp4]name ofhim[/url]');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Posts values ('[url=http://domain.com/video/2001/funny.flv]funny clip[/url]');
Query OK, 1 row affected (0.00 sec)

mysql> select * from Posts;
+--------------------------------------------------------------+
| post                                                         |
+--------------------------------------------------------------+
| [url=http://domain.com/blah.flv]blah video[/url]             |
| [url=http://domain.com/video/name.mp4]name of him[/url]      |
| [url=http://domain.com/video/2001/funny.flv]funny clip[/url] |
+--------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> Update Posts SET Post = CONCAT('[media]',SUBSTRING(post,LOCATE('http',post),LOCATE(']',post)-LOCATE('http',post)) ,'[/media]');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from Posts;
+-------------------------------------------------------+
| post                                                  |
+-------------------------------------------------------+
| [media]http://domain.com/blah.flv[/media]             |
| [media]http://domain.com/video/name.mp4[/media]       |
| [media]http://domain.com/video/2001/funny.flv[/media] |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
ah I see the problem I think.  I need to do a from after to make sure I only get certain records, but not working:

UPDATE Posts 
   SET Post = CONCAT('[media]',SUBSTRING(post,LOCATE('http',post),LOCATE(']',post) - LOCATE('http',post)),
                     '[/media]')
from posts
where post like '%[url=%'
and post like '%[/url]%';

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
created another database and restored the backup there and did your original update.  It updated all the rows to
[media][/media]

Open in new window

0
 
SharathData EngineerCommented:
try this.
UPDATE Posts 
   SET Post = CONCAT('[media]',SUBSTRING(post,LOCATE('http',post),LOCATE(']',post)-LOCATE('http',post)) ,'[/media]') 
 WHERE Post LIKE '[url=%';

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
That worked thanks for sticking with me.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now