The syntax is as follows to trim commas −SELECT TRIM(BOTH ', ' FROM yourColumnName) from yourTableName;Let us see an example −mysql> create table TrimCommasDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> AllTechnicalSkills text    -> ); Query OK, 0 rows affected (0.81 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into TrimCommasDemo(AllTechnicalSkills) values(', C, C++, Java, '); Query OK, 1 row affected (0.14 sec) mysql> insert into TrimCommasDemo(AllTechnicalSkills) values(', MySQL, SQL Server, MongoDB, '); Query OK, 1 row affected (0.13 sec) mysql> ... Read More

You can use CASE statement for this. Let us see an example −mysql> create table BooleanEvaluationDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> FirstValue int,    -> SecondValue int    -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into BooleanEvaluationDemo(FirstValue, SecondValue) values(10, 5); Query OK, 1 row affected (0.20 sec) mysql> insert into BooleanEvaluationDemo(FirstValue, SecondValue) values(15, 20); Query OK, 1 row affected (0.16 sec) mysql> insert into BooleanEvaluationDemo(FirstValue, SecondValue) values(50, 40); Query OK, 1 row affected (0.14 ... Read More

The syntax is as follows to skip the duplicate value and select only one from the duplicated values −select min(yourColumnName1), yourColumnName2 from yourTableName group by yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table doNotSelectDuplicateValuesDemo    -> (    -> User_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> User_Name varchar(20)    -> ); Query OK, 0 rows affected (0.78 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into doNotSelectDuplicateValuesDemo(User_Name) values('John'); Query OK, 1 row affected ... Read More

You can use IN operator for this.The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName IN(‘yourValue1’, ‘yourValue2’, ‘yourValue3’, ...........N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InOperatorDemo    -> (    -> ClientId int    -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into InOperatorDemo values(101); Query OK, 1 row affected (0.19 sec) mysql> insert into InOperatorDemo values(110); Query OK, 1 row affected (0.11 sec) mysql> insert into InOperatorDemo ... Read More

To work with time in PHP/ MySQL, you can use strtotime() function. The PHP code is as follows for the same −$timeValue='8:55 PM'; $changeTimeFormat = date('H:i:s', strtotime($timeValue)); echo("The change Format in 24 Hours="); echo($changeTimeFormat);The snapshot of PHP code is as follows −Here is the output.Here is the MySQL query to get the original time −mysql> SELECT CONCAT('The change Format in 12 Hours in MySQL=', DATE_FORMAT('2019-03-12 20:55:00', '%l:%i %p')) AS OriginalTimeFormat;The following is The output −+------------------------------------------------+ | OriginalTimeFormat ... Read More

You can use hex2bin() function since it is the PHP equivalent of MySQL's UNHEX().The syntax is as follows −$anyVariableName = hex2bin("yourHexadecimalValue");To understand the above syntax, let us implement the above syntax in PHP. The PHP code is as follows −$myFirstValue = hex2bin("7777772E4D7953514C4578616D706C652E636F6D"); var_dump($myFirstValue); $mySecondValue=hex2bin("416476616E6365644A617661576974684672616D65776F726B"); echo(''); var_dump($mySecondValue);The snapshot of PHP code is as follows −Here is the snapshot of The output −Here is the MySQL UNHEX() −Case 1 − The query is as follows −mysql> SELECT UNHEX("7777772E4D7953514C4578616D706C652E636F6D");The following is The output −+---------------------------------------------------+ | UNHEX("7777772E4D7953514C4578616D706C652E636F6D") | +---------------------------------------------------+ | www.MySQLExample.com ... Read More

You can use the following syntax if your column has varchar data type −select yourColumnName FROM yourTableName ORDER BY yourColumnName +0 DESC;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table selectOrderdemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> RankNumber varchar(100)    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into selectOrderdemo(Name, RankNumber) values('Larry', -100); Query OK, 1 row affected (0.23 ... Read More

To delete all rows older than 5 days, you can use the following syntax −delete from yourTableName    where datediff(now(), yourTableName.yourDateColumnName) > 5;Note − Let’s say the current date is 2019-03-10.To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table deleteRowsOlderThan5Demo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Post_Date date    -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into deleteRowsOlderThan5Demo(Name, Post_Date) ... Read More

To remove hyphens using MySQL update, you can use replace() function. The syntax is as follows −update yourTableName    set yourColumnName=replace(yourColumnName, '-', '' );To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeHyphensDemo    -> (    -> userId varchar(100)    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeHyphensDemo values('John-123-456'); Query OK, 1 row affected (0.22 sec) mysql> insert into removeHyphensDemo values('Carol-9999-7777-66555'); Query OK, 1 row affected (0.19 sec) ... Read More

To count the top 10 most occurring values in a column in MySQL, The syntax is as follows −SELECT yourColumnName, count(*)    FROM yourTableName    GROUP BY yourColumnName    ORDER BY count(*) DESC    LIMIT 10;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table countTop10Demo    -> (    -> Value int    -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into countTop10Demo values(10); Query OK, 1 row affected (0.12 sec) ... Read More