Find and replace text in MySQL and Oracle

Today someone told me that they have a blog with over 300 posts and just changed their URL, the problem is that the new blog still had reference to their previous blog's url. And all the images, and reference were to the previous URL which had a 401 re-direct to the new blog. The images, etc were already deleted from the previous blog and imported into the new URL.

He was actually going to update every single entry. I recommended that it the blog has all posts in it's SQL database and had to find all the fields which contained the text (old url address), with the new text (new url ).

Now I will show you and easy way to update this.

Go to your database SQL screen and type in as follows

UPDATE [your_table_name] SET [your_table_field] = REPLACE([your_table_field], '[string_to_find]' , '[string_to_be_replaced]');

example
UPDATE my_posts SET post_content = REPLACE(post_content, 'www.oldurl.com' , 'www.newurl.com');

So armed with only one query per column table combination, you can easily update your blog or database with the new text.

Setting preferred domain

When search engines find your website, they may come across, http://yourdomain.com or http://www.yourdomain.com, which is read as two domains, causing duplicate listings. To fix this you have to decide which web domain will be the preferred domain.

To set your domain in google, go to the www.google.com/webmasters/tools/ website and set your preferred domain.

After you set the preferred domain, you should force everyone to use this domain with a 301 redirect. You do this by editing your root .htaccess file, if hosted on a Linux server. If you do not have a .htaccess file you will need to create one first as follows:

1) Create a text file and name it .htaccess
2) Modify it with the preferred domain code (as details below)
3) Upload it to the root of your server (public www)

If you want people to redirect from non-WWW to WWW:
(for example, if someone types in http://yourdomain.com, it will forward to http://www.yourdomain.com)

RewriteEngine On
RewriteCond %{HTTP_HOST} !^(.*)\.yourdomain\.com$ [NC]
RewriteRule ^(.*)$ http://www.yourdomain.com/$1 [R=301,L]

If you want people to redirect from WWW to non-WWW:
(for example, if someone types in http://www.yourdomain.com, it will forward to http://yourdomain.com)

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www\.yourdomain\.com$ [NC]
RewriteRule ^.*$ http://yourdomain.com%{REQUEST_URI} [R=301,L]

Don't forget to replace “yourdomain” with the URL of your domain, otherwise the code above won't work!

Therefore by setting a preferred domain you are ensuring that Google and all the major search engines will only index your url web links from one domain. This will help your PageRank in search engine rankings.

So don't put it off, set your preferred domain, if you have not done so already! You will see your PageRank rise!

Combine text using Concatenation in Oracle

When wanting to combine text you can use the concatenation operator

Examples:
To place comma after every row returned use as follows:
SELECT table_name ',' FROM user_tables;

You may have a date that you wish to place in a CSV file, you can concentate as follows:
SELECT 'the date today is: ' to_char(sysdate, 'MM-DD-YYYY') ',' FROM sys.dual;