SQL: Search and Replace Text

The way to do a "search and replace" function in SQL is to use the UPDATE command along with the REPLACE command.

The syntax is:
UPDATE table_name SET column_name = REPLACE ('text sought','new text');

For example, I have some old CMS posts that have missing triangle gifs, and I want to replace them with star gifs in WordPress:

UPDATE wp_posts SET post_content = REPLACE (post_content, '/feature/film_reviews/triangle.gif'', '/wp-content/uploads/2015/01/star.gif'');

You should probably do a backup of your database first, and will probably want to test out your code on a single record first, by adding a WHERE clause like: WHERE ID= 99999, or WHERE ID BETWEEN 1000 AND 1005.

SQL: How to REPLACE a String of Characters in a Database Column or Field

I moved some old articles into a WordPress database.

But there were some formatting problems, mostly due to incompatible character codes.

For example, there were non-breaking spaces from Windows that had been used as paragraph indents.

I didn't want them there at all, so I used this code to replace all of the characters using SQL.

wp_posts is the table where Blog Posts are stored.
post_content is the columns where the body of Posts are stored.
"Â Â Â Â  " represents the oddball character translation that I had to replace.

UPDATE wp_posts
SET post_content =
REPLACE (post_content, "Â Â Â Â  ", "")
WHERE post_content LIKE "%Â Â Â Â  %";

SQL: How to SELECT data from two tables, using JOIN

Use a JOIN-ON function to retrieve a data record from related tables

You can pull data from two tables in a relational database using a JOIN function, but each of the tables must have a column of data that is a shared element -- a key in one table is the foreign key in another.

Let's pretend we have a company called Mythological Creatures.

There are two tables that help us keep track of our sales. The foreign key here would be Transactions.itemId, and the key would be Items.id:

SELECT * FROM transactions;

Transactions
id	date	itemId	howMany	totalCost
1001	2013-06-29	6	2	400
1002	2013-06-29	5	1	900
1003	2013-06-30	8	5	1500
1004	2013-06-30	5	2	2400
1005	2013-07-01	7	3	2100

SELECT * FROM Items;

Items
id	nameOfItem	cost
5	Unicorn	1200
6	Harpy	200
7	Chimera	800
8	Minotaur	300

----------------------------------------------------------------

QUESTION #1: How can I see all of our sales?

SOLUTION:

SELECT Transactions.*, Items.*
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id

id	date	itemId	howMany	totalCost	id	nameOfItem	cost
1001	2013-06-29	6	2	400	6	Harpy	200
1002	2013-06-29	5	1	900	5	Unicorn	1200
1003	2013-06-30	8	5	1500	8	Minotaur	300
1004	2013-06-30	5	2	2400	5	Unicorn	1200
1005	2013-07-01	7	3	2100	7	Chimera	800

QUESTION #2: Which creatures did we sell on June 29?

SOLUTION:

SELECT nameOfItem, howMany
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id
WHERE date="2013-06-29";

nameOfItem	howMany
Harpy	2
Unicorn	1

QUESTION #3: How many creatures have we sold, totaled by each type?

SOLUTION:

SELECT nameOfItem, SUM(howMany) AS totalSold
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id
GROUP BY Items.id

nameOfItem	totalSold
Unicorn	3
Harpy	2
Chimera	3
Minotaur	5

QUESTION #4: What is our most profitable type of creature?

SOLUTION:

SELECT nameOfItem, SUM(totalCost) AS totalProfit 

FROM Transactions 

JOIN Items

ON Transactions.itemID = Items.id

GROUP BY Items.id 

ORDER BY totalProfit DESC;

nameOfItem	totalProfit
Unicorn	3300
Chimera	2100
Minotaur	1500
Harpy	400

QUESTION #5: Which transactions have inconsistent sales figures?

SOLUTION:

SELECT Transactions.*, nameOfItem, (howMany * cost) AS shouldHaveBeen, (totalCost - (howMany * cost)) AS difference 
FROM Transactions 
JOIN Items 
ON Transactions.itemID = Items.id 
WHERE Transactions.totalCost <> (howMany * cost) 
GROUP BY Transactions.id

id	date	itemId	howMany	totalCost	nameOfItem	shouldHaveBeen	difference
1002	2013-06-29	5	1	900	Unicorn	1200	-300
1005	2013-07-01	7	3	2100	Chimera	2400	-300