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 342 of 355
MySQL update query to remove spaces between letters?
If you have spaces between letters then you can use REPLACE() function to remove spaces.The syntax is as follows −UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’ ‘, ’’);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId varchar(20), -> UserName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.81 sec)Now insert some records in the table using insert ...
Read MoreREGEX Match integers 6 through 10 in MySQL?
Here you can use BETWEEN operator. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName BETWEEN 6 AND 10;You can use regular expression like this. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '10|[6-9]';To understand the both syntax, let us create a table. The query to create a table is as follows −mysql> create table RegularExpressionDemo -> ( -> Id int -> ); Query OK, 0 rows affected (1.11 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into RegularExpressionDemo values(1); Query OK, ...
Read MoreConvert one base number system to another base system in MySQL
The CONV() function can be used to convert one base number system to another base system.For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.The syntax is as follows −SELECT CAST(CONV('yourColumnName', 16, 10) AS UNSIGNED INTEGER) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table castTypeToBigIntDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Value varchar(100), -> PRIMARY KEY(Id) -> ); Query OK, ...
Read MoreRemove first two characters of all fields in MySQL?
To remove the first two characters of all fields, you need to use SUBSTRING() function from MySQL. The syntax is as follows −UPDATE yourTableName SET yourColumnName=SUBSTRING(yourColumnName, 3) WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RemoveFirstTwoCharacterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StringValue varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.04 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('U:100'); Query OK, 1 ...
Read MoreGet the record of a specific year out of timestamp in MySQL?
You can get year out of timestamp using YEAR() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE YEAR(yourTimestampColumnName)='yourYearValue’';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table getYearOut -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> yourTimestamp timestamp default current_timestamp, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.56 sec)Insert some records in the table using INSERT command−mysql> insert into getYearOut(Name, yourTimestamp) values('John', now()); Query OK, 1 row affected (0.26 sec) ...
Read MoreCan we use IFNULL along with MySQL ORDER BY?
You can use IFNULL along with ORDER BY clause. The syntax is as follows −SELECT *FROM yourTableName ORDER BY IFNULL(yourColumnName1, yourColumnName2);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table IfNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ProductName varchar(10), -> ProductWholePrice float, -> ProductRetailPrice float, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IfNullDemo(ProductName, ProductWholePrice, ProductRetailPrice) values('Product-1', 99.50, ...
Read MoreHow to convert wrongly encoded data to UTF-8 in MySQL?
You need to use CONVERT() function along with binary keyword. The syntax is as follows −SELECT CONVERT(binary CONVERT(yourColumnName using latin1) USING UTF8) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UtfDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(15), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into UtfDemo(Name) values('Obama’s'); Query OK, 1 row affected (0.28 ...
Read MoreMySQL query to group data in the form of user login time per hour and get the records of the users logged in the recent hour?
You can use a subquery with JOIN condition for this. The syntax is as follows −SELECT yourTablevariableName.* FROM ( SELECT MAX(UNIX_TIMESTAMP(yourDateTimeColumnName)) AS anyAliasName FROM getLatestHour GROUP BY HOUR(UserLoginDateTime) ) yourOuterVariableName JOIN yourTableName yourTablevariableName ON UNIX_TIMESTAMP(yourDateTimeColumnName) = yourOuterVariableName.yourAliasName WHERE DATE(yourDateTimeColumnName) = 'yourDateValue';To understand the above syntax and the result to be achieved, let us create a table. The query to create a table is as follows −mysql> create table getLatestHour -> ( -> UserId int, -> UserName varchar(20), -> UserLoginDateTime ...
Read MoreUsing single quotes around database and table name isn’t working in MySQL?
You need to use backticks around table name as well as database name. The syntax is as follows:UPDATE `yourDatabaseName`.`yourTableName` SET yourColumnName1=yourColumnName1+1 WHERE yourColumnName2=’yourValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> use test; Database changed mysql> create table Add1Demo -> ( -> Id varchar(10), -> Value int -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into Add1Demo values('1', 780); Query OK, 1 row affected (0.17 sec) mysql> insert into Add1Demo values('2', ...
Read MoreMySQL GROUP BY with WHERE clause and condition count greater than 1?
To understand the group by with where clause, let us create a table. The query to create a table is as follows −mysql> create table GroupByWithWhereClause -> ( -> Id int NOT NULL AUTO_INCREMENT, -> IsDeleted tinyint(1), -> MoneyStatus varchar(20), -> UserId int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.57 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into GroupByWithWhereClause(IsDeleted, MoneyStatus, UserId) values(0, 'Undone', 101); Query OK, 1 row affected (0.17 sec) mysql> insert into GroupByWithWhereClause(IsDeleted, MoneyStatus, UserId) ...
Read More