To search in a comma separated list, use MySQL find_in_set(). The usage of Regex for this purpose isn’t required here. The syntax is as follows −select *from yourTableName where find_in_set(anyValue, yourColumnName);Let us create a table −mysql> create table demo17 −> ( −> id int not null auto_increment primary key, −> first_name varchar(50), −> value text −> ); Query OK, 0 rows affected (1.81 sec)Insert some records into the table with the help of insert command −mysql> insert into demo17(first_name, value) values('John', '50'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo17(first_name, value) values('David', ''); Query OK, 1 ... Read More

Extract using extract() method along with cast(). Following is the syntax −select extract(minute from cast(yourColumnName as time)) as anyAliasName from yourTableName;Let us create a table −mysql> create table demo15 −> ( −> value time −> ); Query OK, 0 rows affected (2.11 sec)Insert some records into the table with the help of insert command −mysql> insert into demo15 values('10:30:45'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo15 values('06:34:55'); Query OK, 1 row affected (0.17 sec)Display records from the table using select statement −mysql> select *from demo15;This will produce the following output −+----------+ | value ... Read More

To delete duplicate columns, use DELETE with INNER JOIN. Following is the syntax −delete tbl1 from yourTableName anyAliasName1 inner join yourTableName anyAliasName2 where yourCondition1 and yourCondition2Let us create a table −mysql> create table demo14 −> ( −> id int not null auto_increment primary key, −> name varchar(30) −> ); Query OK, 0 rows affected (1.89 sec)Insert some records into the table with the help of insert command −mysql> insert into demo14(name) values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into demo14(name) values('David'); Query OK, 1 row affected (0.18 sec) mysql> insert into demo14(name) values('David'); Query OK, ... Read More

To get the initial, use the concept of left() along with substring_index().Let us create a table −mysql> create table demo13 −> ( −> full_name varchar(100), −> short_name varchar(20) −> ); Query OK, 0 rows affected (1.18 sec)Insert some records into the table with the help of insert command −mysql> insert into demo13(full_name) values('John Smith'); Query OK, 1 row affected (0.27 sec) mysql> insert into demo13(full_name) values('David Miller'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo13(full_name) values('Chris Brown'); Query OK, 1 row affected (0.28 sec)Display records from the table using select statement −mysql> select *from demo13;This ... Read More

Following is the syntax to add months using INTERVAL with Java − MySQL.String query; query = "insert into yourTableName values(curdate()+interval howManyNumberOfMonths month)";Following is the current date −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2020-10-25 | +------------+ 1 row in set (0.00 sec)Let us create a table −mysql> create table demo12 −> ( −> created_date date −> ); Query OK, 0 rows affected (1.84 sec)Here, I am going to add 2 months to the current date from Java. Now, date will be inserted into the table 2020−12−25.The Java code is as followsExampleimport java.sql.Connection; import java.sql.DriverManager; import com.mysql.jdbc.Statement; public ... Read More

To fetch sub strings, use the substr() method in MySQL as in the below syntax −select substr(yourColumnName, startIndex, endIndex) from yourTableName limit anyValue; select substr(yourColumnName, startIndex+endIndex) from yourTableName limit anyValue;Let us create a table −mysql> create table demo11 −> ( −> id int NOT NULL AUTO_INCREMENT PRIMARY KEY, −> fileLocation text −> ); Query OK, 0 rows affected (2.60 sec)Insert some records into the table with the help of insert command −mysql> insert into demo11(fileLocation) values('E:/users/program/sample.sql'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo11(fileLocation) values('E:/users/data/db.sql'); Query OK, 1 row affected (0.32 sec) mysql> insert ... Read More

Yes, for this, use the concept of ArrayList in Java. The syntax is as follows −ArrayList anyVariableName= new ArrayList();Let us create a table −mysql> create table demo10 −> ( −> id int not null auto_increment primary key, −> name varchar(20) −> ); Query OK, 0 rows affected (2.19 sec)Insert some records into the table with the help of insert command −mysql> insert into demo10(name) values('John'); Query OK, 1 row affected (0.23 sec) mysql> insert into demo10(name) values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo10(name) values('David'); Query OK, 1 row affected (0.13 sec)Display records from ... Read More

To merge, use simple join. Following is the syntax −select aliasName1.yourColumnName1, aliasName1.yourColumnName2, . . .N aliasName2.yourColumnName1 from yourTableName1 aliasName1 . . . N join yourTableName2 aliasName2 on yourCondition;Let us create a table −mysql> create table demo8 −> ( −> id int, −> name varchar(20) −> ); Query OK, 0 rows affected (1.77 sec)Insert some records into the table with the help of insert command −mysql> insert into demo8 values(100, 'John'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo8 values(101, 'Mike'); Query OK, 1 row affected (0.16 sec) mysql> insert into demo8 values(102, 'Bob'); Query OK, ... Read More

For this, use GROUP BY along with ORDER BY −select yourColumnName, count(*) as anyAliasName from yourTableName group by yourColumnName order by yourColumnName;Let us create a table −mysql> create table demo7 −> ( −> id int NOT NULL AUTO_INCREMENT, −> first_name varchar(50) −> , −> primary key(id) −> ); Query OK, 0 rows affected (1.22 sec)Insert some records into the table with the help of insert command −mysql> insert into demo7(first_name) values('John'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo7(first_name) values('David'); Query OK, 1 row affected (0.22 sec) mysql> insert into demo7(first_name) values('John'); Query OK, 1 row affected ... Read More

To get rid of LOCK TABLES query, you need to use UNLOCK TABLES.Let us create a table −mysql> create table demo6 −> ( −> country_name varchar(100 −> ) −> ); Query OK, 0 rows affected (1.51 sec)Insert some records into the table with the help of insert command −mysql> insert into demo6 values('US'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo6 values('UK'); Query OK, 1 row affected (0.08 sec) mysql> insert into demo6 values('AUS'); Query OK, 1 row affected (0.11 sec)Display records from the table using select statement −mysql> select *from demo6;This will produce the ... Read More