You can use ZEROFILL for column to fill in or pad with zeros. Let us first create a table−mysql> create table DemoTable    (    Number int    ); Query OK, 0 rows affected (0.58 sec)Following is the query to add zerofill attribute for Number column−mysql> alter table DemoTable change Number Number int(10) zerofill not null; Query OK, 0 rows affected (1.13 sec) Records: 0 Duplicates: 0 Warnings: 0Insert records in the table using insert command −mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(12); Query OK, 1 row affected (0.53 sec) ... Read More

You can use CONCAT() function for this. Let us first create a table −mysql> create table DemoTable    (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(100)    ); Query OK, 0 rows affected (0.43 sec)Insert records in the table using insert command −mysql> insert into DemoTable(UserName) values('John'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(UserName) values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(UserName) values('Robert'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------+----------+ ... Read More

Yes, you can use GREATEST() from MySQL to check maximum from rows (not columns). Let us first create a table −mysql> create table DemoTable    (    Value1 int,    Value2 int,    Value3 int    ); Query OK, 0 rows affected (0.58 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(190, 395, 322); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output−+--------+--------+--------+ | Value1 | Value2 | Value3 | +--------+--------+--------+ | 190 | 395 ... Read More

You can use length() from MySQL to find the size of text stores in a specific column. Let us first create a tablemysql> create table DemoTable    (    CustomerName longtext    ); Query OK, 0 rows affected (0.67 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output−+--------------+ | CustomerName | +--------------+ | Robert       | +--------------+ 1 row in set (0.00 sec)Here is the query to find the size ... Read More

In MySQL, the ENUM data type is used to limit column values to a specified set of options which is useful for categories like colors, status, and gender. Sometimes, it is helpful to display these ENUM values as more descriptive like converting 'P' to "pass" and 'F' to "Fail". The IF() function in MySQL makes this easy by allowing conditional logic within SQL queries. The example below will demonstrate how to use the IF() function to convert ENUM values into user-friendly. Example To understand first let us create a table, DemoTable, which contains an ENUM column UserGender to store ... Read More

To get distinct including whitespace, you can use below syntax −SELECT DISTINCT replace(yourColumnName, ' ', '') FROM yourTableName;Let us first create a table:mysql>create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(20) ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql>insert into DemoTable(Name) values('John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values(' John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values('John'); Query OK, 1 row affected (0.09 sec) mysql>insert into DemoTable(Name) values('Sam'); Query OK, 1 row affected (0.15 sec) ... Read More

Use SUBSTRING() to get first N characters from a MySQL column. Let us first create a table −mysql>create table DemoTable (    Information text ); Query OK, 0 rows affected (2.63 sec)Insert records in the table using insert command −mysql>insert into DemoTable values('MySQL is a structured query language'); Query OK, 1 row affected (0.13 sec)Following is the query to display all records from the table using select statement −mysql>select *from DemoTable;This will produce the following output −+--------------------------------------+ | Information                         | +--------------------------------------+ | MySQL is a structured query ... Read More

You can use STR_TO_DATE() to convert US date format to MySQL format in INSERT. Let us first create a table −mysql>create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ShippingDatetime varchar(200) ); Query OK, 0 rows affected (1.04 sec)Insert some records in the table using insert command. Here, we are using INSERT to convert US date format −mysql>insert into DemoTable(ShippingDatetime) values(STR_TO_DATE('01-31-2012 01:23', '%m-%d-%Y %H:%i')); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(ShippingDatetime) values(STR_TO_DATE('12-01-2018 04:56', '%m-%d-%Y %H:%i')); Query OK, 1 row affected (0.19 sec) mysql>insert into DemoTable(ShippingDatetime) values(STR_TO_DATE('04-17-2019 10:10', '%m-%d-%Y %H:%i')); Query OK, 1 row ... Read More

To check if field exists and then to return the result set, you can use the below syntax −show columns from yourTableName where field='yourColumnName';Let us first create a table −mysql> create table DemoTable (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserFirstName varchar(20),    UserLastName varchar(20),    UserAge int,    UserAddress varchar(200),    UserCountryName varchar(20) ); Query OK, 0 rows affected (0.67 sec)Here is the query to check if field exists and then return the result set −mysql> show columns from DemoTable where field='UserCountryName';This will produce the following output −+-----------------+-------------+------+-----+---------+-------+ | Field           | ... Read More

In order to know the last MySQL error, you can use SHOW command −SHOW ERRORS;Or you can use another syntax −SHOW WARNINGS;Here, we are creating a table that displays an error and then we will find out how to know the last MySQL error. Here, the error occurs because we have intentionally written incorrect create table statement −mysql> create table DemoTable(Id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table DemoTable(Id int)' at line 1Following is the query to ... Read More