You can use substring_index() function from MySQL to split the left part of a string. The syntax is as follows −SELECT yourColumnName1, .....N, SUBSTRING_INDEX(yourColumnName, ’yourSeperatorSymbol’, 1) as anyVariableName from yourTableName;The value 1 indicates that you can get left part of string. To check the above syntax, let us create a table. The query to create a table is as follows −mysql> create table LeftStringDemo -> ( -> Id int, -> Words varchar(100) -> ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using insert ... Read More

To order by a field and list the NULL values first, you need to use the following syntax. This will order in descending order −select yourColumnName from yourTableName group by yourColumnName is null desc, yourColumnName desc;To understand the above syntax, let us first create a table −mysql> create table OrderByNullFirstDemo    −> (    −> StudentId int    −> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into OrderByNullFirstDemo values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into OrderByNullFirstDemo ... Read More

To select timestamp data range, use the below syntax −SELECT *FROM yourTableName where yourDataTimeField >= anyDateRange and yourDataTimeField < anyDateRangeTo understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DateRange −> ( −> DueTime timestamp −> ); Query OK, 0 rows affected (1.34 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DateRange values('2016-11-13'); Query OK, 1 row affected (0.51 sec) mysql> insert into DateRange values('2016-10-14'); Query OK, 1 row ... Read More

To begin auto increment from a specific point, use ALTER command. The syntax is as follows −ALTER TABLE yourTableName auto_increment = anySpecificPoint;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table AutoIncrementSpecificPoint −> ( −> BookId int auto_increment not null, −> Primary key(BookId) −> ); Query OK, 0 rows affected (0.56 sec)Now you can insert records using insert command.The query is as follows −mysql> insert into AutoIncrementSpecificPoint values(); Query OK, 1 row affected (0.17 sec) ... Read More

To implement a keyword search in MySQL, you can use LIKE operator. The syntax is as follows −SELECT *FROM yourTableName where yourColumnName Like ‘%anyKeywordName%’ or yourColumnName Like ‘%anyKeywordName%’;To understand it further, let us first create a table. The following is the query to create a table −mysql> create table KeywordSearchDemo    −> (    −> StudentId int    −> ,    −> StudentName varchar(100)    −> ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using INSERT command. The query to insert record is as follows −mysql> insert into KeywordSearchDemo values(100, 'Adam John'); Query OK, 1 ... Read More

The concat operator can be used in ORACLE. MySQL use concat() function to perform concatenation.To understand the concat() function, let us create a table. The query to create a table is as follows −mysql> create table ConcatenationDemo −> ( −> Id int, −> Name varchar(100) −> ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ConcatenationDemo values(100, 'John'); Query OK, 1 row affected (0.19 sec) mysql> insert into ConcatenationDemo values(101, 'Sam'); Query ... Read More

You do not need to use values whenever you insert the results of a select. To insert the results of select, let us first create two tables.The first table query is as follows −< FirstTableDemo> mysql> create table FirstTableDemo    −> (    −> StudentId int,    −> StudentName varchar(100)    −> ); Query OK, 0 rows affected (0.41 sec)Now create second table and after that insert the second table records in the first table using INSERT SELECT command.The query to create the second table − mysql> create table SecondTableDemo    −> (    −> Id int,    −> ... Read More

The MySQL LIMIT is applied after ORDER BY. Let us check the limit condition. Firstly, we will create a table −mysql> create table LimitAfterOrderBy    −> (    −> Id int,    −> Name varchar(100)    −> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into LimitAfterOrderBy values(101, 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert into LimitAfterOrderBy values(102, 'Carol'); Query OK, 1 row affected (0.15 sec) mysql> insert into LimitAfterOrderBy values(103, 'Bob'); Query OK, 1 row affected (0.21 sec) ... Read More

MySQL alias cannot be used with *. However, it can be used for individual column. The syntax is as follows −select anyaliasName.yourColumnName1 as anyaliasName1, anyaliasName.yourColumnName2 as anyaliasName2, anyaliasName.yourColumnName3 as anyaliasName3, anyaliasName.yourColumnName4 as anyaliasName4, . . . . N from yourTableName as anyaliasName;MySQL alias is a variable of table that can be used to access the column name of that particular table. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table TableAliasDemo    −> (    −> Id int,    −> Name varchar(100),    −> Age int    −> ... Read More

You can use in-built function STR_TO_DATE() from MySQL. The syntax is as follows −SELECT STR_TO_DATE(CONCAT(yourYearColumName, '-', LPAD(yourMonthColumName, 2, '00'), '-', LPAD(yourDayColumName, 2, '00')), '%Y-%m-%d') 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 DateCreateDemo    −> (    −> `Day` varchar(2),    −> `Month` varchar(2),    −> `Year` varchar(4)    −> ); Query OK, 0 rows affected (1.68 sec)Insert values for all fields using insert command. The query is as follows −mysql> insert into DateCreateDemo values('15', '12', '2018'); Query OK, 1 row affected (0.09 ... Read More