MySQL: Count occurrences of words in a column
- Joel Lipman
- MySQL
- Hits: 4200
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\\_]+$';
-- where @ThisSearch is a posted (and sanitized) variable SET @ThisSearch:="Brains"; SELECT columnID, columnFullName, CASE WHEN columnFirstName LIKE @ThisSearch THEN 20 WHEN columnFullName LIKE @ThisSearch THEN 10 WHEN columnLastName LIKE @ThisSearch THEN 10 WHEN columnFullName LIKE @ThisSearch THEN 1 END as relevance FROM myTable WHERE s.columnPublished
Suppose you have a column in your table that you use as a counter (storing the value of the counter - eg. times an...
What? We have a specific timetabling system for academic institutions and all our staff/students follow academic week...
Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all...
What? A quick article showing my MySQL statement when I want to remove all the accents from foreign characters and...
What? So this is a quick article on how to delete from multiple tables in a mySQL database where we use some JOIN...
Given Data Activities by employees with start dates and end dates in a mySQL database. Objective User Mon Tue Wed Thu Fri Sat Sun Total --------------- ------- ------- ------- ------- -------...
Hopefully the title doesn't put you off but after much Googling and Bing-ing, I still couldn't figure out how to do...
would first using client table zoho name display creator license joomla create note time added form parameter order code file used windows work files find version value list mysql source need field date google error website function following system report deluge script uploaded database server user data where page solution JoelLipman.Com