MySQL: Count occurrences of words in a column
- Joel Lipman
- MySQL
- Hits: 3483
This is an article to document how to output the most frequently used words in a MySQL database column.
How?
Note this only applies to MySQL.
Sometimes it is critical to ensure a group of queries are all executed successfully to maintain the integrity of our data. Let's say we have a banking app where we need to subtract funds from one account and add funds to another:
$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'-1000000 WHERE 'user' = 'Bob'"); $mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'+1000000 WHERE 'user' = 'Fred'");
What if one of the queries fails? To mitigate the chances of misplacing our millions due to some possible error we must ensure that both queries are executed with the expected result before committing any changes to the data. This where transactions are useful. We can run the queries, then check if some conditions are met before committing the changes in the data:
UPDATE `mytable` SET `myColumn` = TRIM(CHAR(9) FROM TRIM(`myColumn`));Source: Stack Overflow - How to Remove Tabs
UPDATE `table_to_update` a INNER JOIN `table_to_read` b ON a.`CountryName` COLLATE utf8_general_ci LIKE b.`CountryName` COLLATE utf8_general_ci SET a.`ccTLD` = b.`ccTLD`
ThisDate ThisDay StartTime TimeOut TimeIn EndTime TotalTimeToday TotalTimeWeek ----------- ----------- ---------- -------- ------- -------- -------------- ------------- 2013-12-02 Monday 09:00 12:00 13:00 17:00 7:00 7:00 2013-12-03 Tuesday 08:45 12:00 13:30 17:45 7:30 14:30 2013-12-04 Wednesday 09:00 12:30 13:30 17:00 7:00 21:30 2013-12-05 Thursday 10:00 12:15 12:45 17:15 7:45 29:15 2013-12-06 Friday 07:00 12:00 13:00 16:30 8:30 37:45 2013-12-07 Saturday - - - - 0:00 37:45 2013-12-08 Sunday 03:00 04:00 - - 4:00 41:45
-- return all records that contain non-alphanumeric characters SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$'; -- return all records that are non-alphanumeric but ignore underscores SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\\_]+$';
A data type reference table. If you're designing a database then you don't need me to tell you what this is. My...
What? So this is a quick article on how to delete from multiple tables in a mySQL database where we use some JOIN...
What? Week Ending Date has been requested a lot more frequently now. It's an odd one but the example below shows how...
The Why So I find myself writing increasingly complex SQL scripts and it's at the stage where we need to optimize the...
I've been looking into this for a friend and going through forums to investigate this error. After you've checked your...
What? Quick note on how to do this. I was tasked with cleaning up an english database by replacing all special...
What? This article is to remind me how to create a blank weekly timesheet which reads the duration of events from a...
value deluge website field code create time display system google function work where using report used windows find data zoho script creator solution first name uploaded database files need license version joomla following mysql source table would error parameter user file order page date form server note could added list JoelLipman.Com