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

You can use IF() for this. Let us first create a table. One of the columns here is having ENUM typemysql> create table DemoTable    (    UserId int,    UserName varchar(40),    UserGender ENUM('M', 'F')    ); Query OK, 0 rows affected (1.11 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(1, 'John', 'M'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(2, 'Maria', 'F'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(3, 'David', 'M'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(4, 'Emma', ... 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

To create database only once, use the below syntax.CREATE DATABASE IF NOT EXISTS yourDatabaseName;To create table only once, use the below syntax −CREATE TABLE IF NOT EXISTS yourTableName (    yourColumnName yourDatatype,    .    .    .    N );Let us implement both the above syntaxes to create database and table only once if does not already exist −mysql> CREATE DATABASE IF NOT EXISTS login; Query OK, 1 row affected (0.23 sec)Above query creates a database successfully.Following is the query to create a table −mysql> CREATE TABLE IF NOT EXISTS DemoTable (    Id int ); Query OK, 0 rows affected (0.56 sec)Above query creates a table successfully.

You can use IF in stored procedure and IF() in select statement as well.IF() in select statementIF() in select statement mysql> select if(0=0, 'Hello MySQL', 'condition is wrong');This will produce the following output −+------------------------------------------------------+ | if('test'='test', 'Hello MySQL', 'condition is wrong') | +------------------------------------------------------+ | Hello MySQL | +------------------------------------------------------+ 1 row in set (0.00 sec)The second case if your condition ... Read More

To match underscore in a MySQL string, you can use the below syntax −select *from yourTableName where yourColumnName LIKE '%\_%';Let us first create a table −mysql> create table DemoTable (    ClientId varchar(200) ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('CLI_101'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('CLI1110'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('_CLI102'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('CLI103_'); Query OK, 1 row affected (0.12 sec) mysql> insert into ... Read More