Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQL Articles
Page 298 of 355
How to strip all spaces from a column in MySQL?
To strip all spaces from a column in MySQL, you can use REPLACE() function. Following is the syntax −update yourTableName set yourColumnName=REPLACE(yourColumnName, ' ', '' );Let us first create a table −mysql> create table stripAllSpacesDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.56 sec)Following is the query to insert records in the table using insert command −mysql> insert into stripAllSpacesDemo(Name) values('Jo h n'); Query OK, 1 row affected (0.19 sec) mysql> insert into stripAllSpacesDemo(Name) values(' Joh n'); Query OK, 1 row affected (0.16 ...
Read MoreSelect rows having more than 2 decimal places in MySQL?
To select rows with more than 2 decimal places, use SUBSTR() function from MySQL. Let us first create a table −mysql> create table selectRows2DecimalPlacesDemo -> ( -> Amount varchar(100) -> ); Query OK, 0 rows affected (0.73 sec)Following is the query to insert records in the table using insert command −mysql> insert into selectRows2DecimalPlacesDemo values('234.5678'); Query OK, 1 row affected (0.17 sec) mysql> insert into selectRows2DecimalPlacesDemo values('19.50'); Query OK, 1 row affected (0.19 sec) mysql> insert into selectRows2DecimalPlacesDemo values('23.456'); Query OK, 1 row affected (0.17 sec) mysql> insert into selectRows2DecimalPlacesDemo values('12.123'); Query OK, 1 row affected (0.14 ...
Read MoreHow to arrange data in s specific order in MySQL?
Use ORDER BY IF() to arrange data in a specific order. Following is the syntax −select *from yourTableName ORDER BY IF(yourColumnName=yourValue1 OR yourColumnName=yourValue2 OR yourColumnName=yourValue3, yourColumnName, ~yourColumnName) ASC;Let us first create a table −mysql> create table arrangeDataInSpecificOrder -> ( -> StudentId int, -> StudentName varchar(20) -> ); Query OK, 0 rows affected (0.64 sec)Following is the query to insert some records in the table using insert command −mysql> insert into arrangeDataInSpecificOrder values(10, 'Larry'); Query OK, 1 row affected (0.12 sec) mysql> insert into arrangeDataInSpecificOrder values(15, 'Mike'); Query OK, 1 row affected (0.09 sec) mysql> insert into ...
Read MoreHow to delete all rows except some in MySQL?
You can use NOT IN operator for the rows you do not want to delete. Following is the syntax −delete from yourTableName where yourColumnName NOT IN(‘yourValue1’, ‘yourValue2’, ‘yourValue3’, .........N);Let us first create a table −mysql> create table deleteAllRowsWithCondition -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.84 sec)Following is the query to insert some records in the table using insert command −mysql> insert into deleteAllRowsWithCondition(Name) values('Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into deleteAllRowsWithCondition(Name) values('John'); Query OK, 1 row affected ...
Read MoreHow to find if a column is auto_increment in MySQL?
To find if a column is auto_increment in MySQL, you can use the following syntax −select COLUMN_NAME from information_schema.columns where TABLE_SCHEMA='yourDatabaseName' and TABLE_NAME='yourTableName' and EXTRA like '%auto_increment%';Let us first create a table. Here, ClientId is set AUTO_INCREMENT −mysql> create table autoIncrementTableDemo -> ( -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(20), -> ClientAge int, -> ClientAddress varchar(100), -> ClientCountryName varchar(100) -> ); Query OK, 0 rows affected (0.61 sec)Now, let us find whether any of the column is auto_increment −mysql> select COLUMN_NAME from information_schema.columns where TABLE_SCHEMA='test' and TABLE_NAME='autoIncrementTableDemo' and EXTRA ...
Read MoreMySQL query to find sum of fields with same column value?
Use GROUP BY clause for this. Let us first create a table −mysql> create table sumOfFieldsDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientSerialNumber varchar(100), -> ClientCost int -> ); Query OK, 0 rows affected (0.50 sec)Following is the query to insert some records in the table using insert command −mysql> insert into sumOfFieldsDemo(ClientSerialNumber, ClientCost) values('1111', 450); Query OK, 1 row affected (0.16 sec) mysql> insert into sumOfFieldsDemo(ClientSerialNumber, ClientCost) values('2222', 550); Query OK, 1 row affected (0.15 sec) mysql> insert into sumOfFieldsDemo(ClientSerialNumber, ClientCost) values('3333', 150); Query OK, 1 row affected (0.64 ...
Read MoreUpdate multiple rows in a single column in MySQL?
To update multiple rows in a single column, use CASE statement. Let us first create a table −mysql> create table updateMultipleRowsDemo -> ( -> StudentId int, -> StudentMathScore int -> ); Query OK, 0 rows affected (0.63 sec)Following is the query to insert records in the table using insert command −mysql> insert into updateMultipleRowsDemo values(10001, 67); Query OK, 1 row affected (0.14 sec) mysql> insert into updateMultipleRowsDemo values(10002, 69); Query OK, 1 row affected (0.15 sec) mysql> insert into updateMultipleRowsDemo values(10003, 89); Query OK, 1 row affected (0.14 sec) mysql> insert into updateMultipleRowsDemo values(10004, 99); Query ...
Read MoreHow to sort more than one column at a time in MySQL?
To sort more than one column at a time, you can use ORDER BY clause. Following is the syntax −select yourColumnName1, yourColumnName2, yourColumnName3 from yourTableName order by yourColumnName2, yourColumnName3;Let us first create a table −mysql> create table doubleSortDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100), -> StudentCountryName varchar(10) -> ); Query OK, 0 rows affected (0.71 sec)Following is the query to insert records in the table using insert command −mysql> insert into doubleSortDemo(StudentName, StudentCountryName) values('John', 'AUS'); Query OK, 1 row affected (0.21 sec) mysql> insert into doubleSortDemo(StudentName, StudentCountryName) values('Sam', ...
Read MoreMySQL query to display all the fields that contain a capital letter?
To display all the fields that contain a capital letter, use the RLIKE that performs a pattern match of a string expression against a pattern.Let us first create a table −mysql> create table contains_capital_letterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100) -> ); Query OK, 0 rows affected (1.42 sec)Following is the query to insert some records in the table using insert command −mysql> insert into contains_capital_letterDemo(Name) values('Larry'); Query OK, 1 row affected (0.17 sec) mysql> insert into contains_capital_letterDemo(Name) values('larry'); Query OK, 1 row affected (0.12 sec) mysql> ...
Read MoreMySQL search if more than one string contains special characters?\\n
To search if strings contain special characters, you can use REGEXP. Following is the syntax −select * from yourTableName where yourColumnName REGEXP '[^a-zA-Z0-9]';Let us first create a table −mysql> create table specialCharactersDemo -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.58 sec)Insert records in the table using insert command. Following is the query −mysql> insert into specialCharactersDemo values('STU_1234'); Query OK, 1 row affected (0.15 sec) mysql> insert into specialCharactersDemo values('STU567'); Query OK, 1 row affected (0.14 sec) mysql> insert into specialCharactersDemo values('STU#1234'); Query OK, 1 row affected (0.13 sec) mysql> insert into specialCharactersDemo ...
Read More