To replace all NULL values in a particular field of a particular table, use UPDATE command with IS NULL property. The syntax is as follows:UPDATE yourTableName SET yourColumnName=”yourValue’ WHERE yourColumnName IS NULL;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table Employee_Information_Table    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Salary int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query to insert record is as ... Read More

To remove all characters after the last comma in the string, you can use SUBSTRING_INDEX().If you do not know the location of the last comma, then you need to find the last comma dynamically using LENGTH().The syntax is as follows −UPDATE yourTableName set yourColumnName = SUBSTRING_INDEX(yourColumnName, ', ', LENGTH(yourColumnName) - LENGTH(REPLACE(yourColumnName, ', ', '')));To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RemoveAllCharacters    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FullInfo varchar(200),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 ... Read More

You can use DISTINCT with RLIKE operator to find a list of city names that do not start with vowels.The syntax is as follows −SELECT DISTINCT yourCityColumnName FROM yourTableName WHERE yourCityColumnName NOT RLIKE ‘ ^[AEIOUaeiou].*$’;To understand the above syntax, let us create a table. Here, we have a column for city names.The query to create a table is as follows −mysql> create table Employee_Information    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> EmployeeName varchar(20),    -> CityName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the ... Read More

To change the root username in MySQL, you need to use UPDATE and SET command. The syntax is as follows −UPDATE user set user = ’yourNewUserName’ WHERE user = ’root’;To understand the above syntax, let us switch the database to MySQL using USE command.The query is as follows to switch the database.mysql> use mysql; Database changedNow list all the users from MySQL.user table. The query is as follows −mysql> select user from MySQL.user;The following is the output −+------------------+ | user             | +------------------+ | Manish           | | User2     ... Read More

You can count values from comma-separated field using CHAR_LENGTH() method from MySQL. The syntax is as follows −SELECT *, (CHAR_LENGTH(yourColumnName) - CHAR_LENGTH(REPLACE(yourColumnName, ', ', '')) + 1) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CountValuesCommaSeparated    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> CommaSeparatedValue text,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.76 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue) values('101, 104, ... Read More

To update an entire row in MySQL, use UPDATE command. You need to know the primary key column. The syntax is as follows to update an entire row.UPDATE yourTableName SET yourColumnName1 = ’yourValue1’ ,yourColumnName2 = ’yourValue2’ ,    yourColumnName3 = ’yourValue3’ ,.......................N    WHERE yourPrimaryKeyColumnName = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UpdateEntireRowDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> Marks int,    -> PRIMARY KEY(Id)    -> ); Query OK, ... Read More

There are lots of options available to replace NULL values using select statement. You can use CASE statement or IFNULL() or COALESCE()Case 1 − Using IFNULL()The syntax of IFNULL() is as follows −SELECT IFNULL(yourColumnName, ’yourValue’) AS anyVariableName from yourTableName;Case 2 − Using COALESCE()The syntax of COALESCE() is as follows −SELECT COALESCE(yourColumnName, ’yourValue’) AS anyVariableName from yourTableName;Case 3 − Using CASE statementThe syntax of CASE statement.SELECT CASE WHEN yourColumnName IS NULL THEN ‘yourValue’ ELSE yourColumnName END AS anyVariableName FROM yourTableNameTo understand what we discussed above, let us create a table. The query to create a table is as follows −mysql> create ... Read More

In order to work with hex, use the CONV() function to convert between bases. The syntax is as follows −SET anyVariableName = CONV(yourHexValue, 16, 10);To understand the above syntax, let us create a stored procedure. The query to create a stored procedure is as follows −mysql> DELIMITER // mysql> CREATE PROCEDURE SP_HEX_TO_DEC( HEXVALUE VARCHAR(10) )    -> BEGIN    -> DECLARE Decimalvalue INTEGER;    -> SET Decimalvalue = CONV(HEXVALUE, 16, 10);    -> select Decimalvalue;    -> END;    -> // Query OK, 0 rows affected (0.19 sec) mysql> DELIMITER ;The above stored procedure converts the hexadecimal to decimal. As ... Read More

You can use CREATE TABLE command to create a table from an existing table. The syntax is as follows:CREATE TABLE yourNewTableName LIKE yourOldTableNameTo understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ShowCreateTableCommand    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FirstName varchar(20),    -> LastName varchar(20),    -> Age int,    -> TotalMarks int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.22 sec)Here is the query to describe a table using DESC command. The query is as follows:mysql> desc ... Read More

You need to use ALTER command to change collation to utf8_bin. The syntax is as follows:ALTER TABLE yourTableName COLLATE utf8_general_ci;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table CollateDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.98 sec)Check the DDL of the table. The syntax is as follows:SHOW CREATE TABLE yourTableName;Let us now check the DDL of our table:mysql> show create table CollateDemo;The following is ... Read More