Database - Joel Lipman .Com

MySQL: Count occurrences of words in a column

: Joel Lipman; MySQL; 12 March 2020; Hits: 5456

What?
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.

: Ike Francis; MySQL; 14 December 2016; Hits: 3440

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:

copy raw

$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'-1000000 WHERE 'user' = 'Bob'");
$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'+1000000 WHERE 'user' = 'Fred'");

$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:

Category: MySQL :: Article: 644

: Joel Lipman; MySQL; 13 June 2016; Hits: 1947

What?
A quick article on how to trim in MySQL along with getting rid of any leading or trailing tab characters.

Why?
I use MS Excel for organizing data and then converting to MySQL commands. Unfortunately, the MS Excel software adds tab characters to delimit the columns...

How?

UPDATE `mytable` SET `myColumn` = TRIM(CHAR(9) FROM TRIM(`myColumn`));

Source: Stack Overflow - How to Remove Tabs

Convert foreign characters to English alphabet

: Joel Lipman; MySQL; 13 June 2016; Hits: 2916

What?
A quick article showing my MySQL statement when I want to remove all the accents from foreign characters and return the English equivalent.

Why?
A content management system (CMS) that I'm working on has just gone international and started including the names of places in other countries. This is nice but its search engine doesn't work properly as it thinks "riviere" is different to "rivière". We need to ensure that a search for any of these kind of words will return results of similarly typed/sounding words.

How?
Here's just a splurge of SQL but I use this often enough:

: Joel Lipman; MySQL; 22 October 2014; Hits: 4181

What?
A quick article on how to populate a database column from another table using a string comparison.

Why?
I have several database tables which replicate country names and I would rather they all use the ccTLD two letter code. This article was written because it took me so long to work it out.

How?

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`

T-SQL functions to convert Strings to Tables

: Joel Lipman; Transact-SQL; 20 August 2014; Hits: 6505

Applies to:

Microsoft SQL Server 2008 R2
Microsoft SQL Server 2012

What?
These were in a solution and I thought I'd note them on my site so I can refer to them more easily.

How?

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

: Joel Lipman; MySQL; 28 July 2014; Hits: 159114

Applies to:

MySQL Database v5.0.45
MySQL Workbench v6.0.8.11354 build 833

What?
This is a quick article on how to get around the problem of backing up your MySQL database when attempting to "Data Export" using MySQL Workbench. This is not regarding the connection issue as I can connect to my database using MySQL Workbench (I have enabled the old authentication protocol). The error ONLY appears when I try to "data export" the database.

Why?
The quick solution for everyone else is to change/reset the password of the connecting database user, but herein lies the problem. When you read my workaround, you'll say that I haven't solved anything; but this is a production database I want to export for offline backup. I have to raise and log a formal change request and follow a workflow process in order to make a change on a live system to a service user account... Especially one used by the scripts to access the database-driven website.

How?

: Joel Lipman; Transact-SQL; 04 June 2014; Hits: 25469

What?
So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a personnel feed so it won't be converting long paragraphs of English text. Instead it will be applied to names and addresses as well as job titles and departments.

Why?
We wanted a T-SQL version despite having successfully built a custom script component for SSIS.

How?
You can search my site for how to do this in VB or C#. This version is entirely using T-SQL and we're going to use a function so no dynamic SQL (execute, evaluate).

: Joel Lipman; Transact-SQL; 27 March 2014; Hits: 24226

What?
This took me a while to find so I've posted an article below detailing how to parse or extract values from a string containing XML code.

Why?
I'm working with a system which stores XML strings in a database and rather than a separate file, it stores these in a row.

How?

: Joel Lipman; Databases; 09 January 2014; Hits: 5739

What?
Practice makes perfect. Or in my case, any practice is a start. This article serves as a quick note on how to use regular expressions within SQL statements:

How?
For the following examples, I am pretending to select rows from a table called `STUDENTS`.

: Joel Lipman; MySQL; 05 December 2013; Hits: 2603

What?
This article is to remind me how to create a blank weekly timesheet which reads the duration of events from a database and auto-completes your timesheet.

Why?
I'm being tasked to work with EPM (Microsoft Enterprise Project Management) more and more. Similar systems have popped out that support some form of time recording and activity logging. The example below however is within a LAMP/MySQL environment but the SQL basics are here to help me adapt it to whatever environment people keep throwing at me.

What I want:

copy raw

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

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

Category: MySQL :: Article: 541

: Joel Lipman; Oracle PL/SQL; 05 December 2013; Hits: 15004

What?
A quick note on how I got round one this one.

Why?
Often enough, our requirement is that the latest record from another table is associated with the current row, and often enough we get the latest by ordering the dataset of the subquery. In T-SQL and MySQL, this is not so much of an issue.

I get this error when having to use an ORDER BY clause in a subquery within an Oracle 11g environment.

How?
Consider the following:

MySQL: Count occurrences of words in a column

MySQL Transactions in PHP

Trim values along with the tab

Convert foreign characters to English alphabet

MySQL Updating a database table from another table

T-SQL functions to convert Strings to Tables

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

Convert to Proper Case in T-SQL

T-SQL: Parse an XML value

Regular Expressions in SQL

Generate a Timesheet in MySQL

Oracle: order by subquery missing right parenthesis

MySQL

Oracle PL/SQL

Transact-SQL

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support