You can remove trailing zeros using TRIM() function. The syntax is as follows.SELECT TRIM(yourColumnName)+0 FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeTrailingZeroInDecimal    -> (    -> Id int not null auto_increment,    -> Amount decimal(5, 2),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeTrailingZeroInDecimal(Amount) values(405.50); Query OK, 1 row affected (0.22 sec) mysql> insert into removeTrailingZeroInDecimal(Amount) values(23.05); Query OK, ... Read More

To generate serial number i.e. row count in MySQL query, use the following syntax.SELECT @yourVariableName − = @yourVariableName+1 anyAliasName,    yourColumnName1, yourColumnName2, yourColumnName3, ....N from yourTableName ,    (select @yourVariableName − = 0) as yourVariableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table tblStudentInformation    -> (    -> StudentName varchar(20),    -> StudentAge int,    -> StudentMathMarks int    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into tblStudentInformation values('Carol', ... Read More

To get the table column names in alphabetical order, you need to use ORDER BY. The syntax is as follows −SELECT anyReferenceName.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS anyReferenceName WHERE anyReferenceName.TABLE_NAME = ’yourTableName’ ORDER BY anyReferenceName.COLUMN_NAMEFirst, we need to get all the columns and then we need to use ORDER BY. In the above query, we are getting all columns using INFORMATION_SCHEMA.COLUMNS.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ColumnsOrder    -> (    -> StudentFirstName varchar(20),    -> Id int,    -> StudentAge int,    -> StudentLastName varchar(20)   ... Read More

To check a string contains numbers, you can use regexp i.e. Regular Expressions. The syntax is as follows −SELECT *FROM yourTableName where yourColumnName REGEXP ‘[0-9]’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StringContainsNumber    -> (    -> Id int not null auto_increment,    -> Words text,    -> primary key(Id)    -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StringContainsNumber(Words) values('He12345llo'); Query OK, 1 row affected (0.19 sec) ... Read More

Equivalent of Microsoft SQL Server IDENTITY column in MySQL is AUTO_INCREMENT. The IDENTITY in SQL Server acts like AUTO_INCREMENT in MySQL.The syntax is as follows −CREATE TABLE yourTableName (    yourColumnName1 dataType NOT NULL AUTO_INCREMENT,    yourColumnName2 dataType,    .    .    .    N,    PRIMARY KEY(yourColumnName1) );In MySQL, you need to use primary key if your column is auto_increment otherwise MySQL will give an error. Look at the error −mysql> create table EquivalentOfIdentityInMySQL    -> (    -> ProductId int NOT NULL AUTO_INCREMENT,    -> ProductName varchar(30)    -> ); ERROR 1075 (42000) − Incorrect table definition; ... Read More

You can update two columns using SET command separated with comma(, ). The syntax is as follows −UPDATE yourTableName SET yourColumnName1 = ’yourValue1’, yourColumnName2 = ’yourValue2’ where yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StudentInformations    -> (    -> StudentId int not null auto_increment,    -> StudentFirstName varchar(20),    -> StudentLastName varchar(20),    -> Primary Key(StudentId)    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StudentInformations(StudentFirstName, StudentLastName) ... Read More

To change the year in MySQL date, you need to use DATE_FORMAT() function with UPDATE command. The syntax is as follows.UPDATE yourTableName SET yourDateColumnName = DATE_FORMAT(yourDateColumnName ,'yourYearValue-%m-%d');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ChangeYear    -> (    -> id int not null auto_increment,    -> ArrivalTime date,    -> PRIMARY KEY(id)    -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into ChangeYear(ArrivalTime) values(date_add(now(), interval -2 year)); Query OK, 1 row affected, 1 warning ... Read More

Order by the choice of strings you want, using the FIELD() function. The syntax is as follows −SELECT *FROM yourTableName ORDER BY FIELD(yourColumnName, ’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 OrderByListOfStrings    -> (    -> Id int not null auto_increment,    -> CarName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByListOfStrings(CarName) values('Ford'); Query OK, 1 row affected ... Read More

To get the output MySQL query result in CSV format, use concat_ws(). The syntax is as follows −SELECT CONCAT_WS(‘, ’, yourColumnName1, yourColumnName2, yourColumnName3, ....N) 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 CSVFormatOutputs    -> (    -> StudentId int not null auto_increment,    -> StudentName varchar(20),    -> StudentAge int,    -> PRIMARY KEY(StudentId)    -> ); Query OK, 0 rows affected (1.15 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CSVFormatOutputs(StudentName, StudentAge) ... Read More

To order by like in MySQL, use the case statement. The syntax is as follows −SELECT *FROM yourTableName    ORDER BY CASE    WHEN yourColumnName like '%yourPatternValue1%' then 1    WHEN yourColumnName like '%yourPatternValue2%' then 2 else 3 end;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByLikeDemo    -> (    -> Id int,    -> Name varchar(100)    -> ); Query OK, 0 rows affected (1.84 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByLikeDemo values(100, ... Read More