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
-
Economics & Finance
Articles on Trending Technologies
Technical articles with clear explanations and examples
Can we replace all the digits of column values to zero except the first digit?
Yes, we can replace all the digits of column values to zero except the first digit. Let us first see an example and create a table −mysql> create table DemoTable744 (Number varchar(100)); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable744 values('537737736252'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable744 values('989000099999'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable744 values('343225666666664533'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable744 values('4322111899494'); Query OK, 1 row affected (0.15 sec)Display all records from the table ...
Read MoreMySQL query to keep only first 2 characters in column value and delete rest of the characters?
To keep only first two characters and delete rest of the characters, use SUBSTRING().Let us first create a table −mysql> create table DemoTable743 (SubjectName varchar(100)); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable743 values('MySQL'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable743 values('Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable743 values('MongoDB'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable743 values('Python'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable743 values('Data Structure'); Query OK, 1 row affected (0.68 ...
Read MoreFind the records with % character in a LIKE query with MySQL
Let us first create a table −mysql> create table DemoTable742 (Value varchar(100)); Query OK, 0 rows affected (2.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable742 values('632535MIT'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable742 values('US%UK'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable742 values('56%78'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable742 values('%_1234'); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select *from DemoTable742;This will produce the following output -+-----------+ | Value | ...
Read MoreHow to subtract the same amount from all values in a column with MySQL?
Let us first create a table −mysql> create table DemoTable741 (Number int); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable741 values(70); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable741 values(55); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable741 values(89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable741 values(79); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable741 values(34); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable741;This will ...
Read MoreFind records with double quotes in a MySQL column?
Use LIKE to find records with double quotes. Following is the syntax −select *from yourTableName where yourColumnName LIKE '%"%';Let us first create a table −mysql> create table DemoTable740 (Value varchar(100)); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable740 values("""); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable740 values(""John"); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable740 values("Sam"); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable740;This will produce the following output -+-------+ | ...
Read MoreFinding the sum of integers from multiple MySQL rows in same column?
Let us first create a table −mysql> create table DemoTable739 (Price int); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable739 values(100); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable739 values(50); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable739 values(1200); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable739 values(500); Query OK, 1 row affected (0.37 sec) mysql> insert into DemoTable739 values(800); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable739;This will ...
Read MoreMatch optional end of line after every record with REGEXP?
Let us first create a table −mysql> create table DemoTable(EmployeeCode varchar(100)); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('EMPLOYEE:100 John Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('EMPLOYEE:16537 Chris Brown'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('EMPLOYEE:100 David Miller'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('EMPLOYEE:100 23432 David Miller'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql>; select *from DemoTable;This will produce the following output ...
Read MoreUsing Regex find strings containing a-z, A-Z and 0-9 in MySQL
To find strings containing a-z, A-Z and 0-9, use BINARY REGEXP along with AND operator.Let us first create a table −mysql> create table DemoTable738 (UserId varchar(100)); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable738 values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable738 values('sAm456'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable738 values('98Carol'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable738 values('67david'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable738 values('69MIKE'); Query OK, 1 row affected (0.18 ...
Read MoreHow to create an empty VIEW in MySQL?
To create an empty view in MySQL, following is the syntax −create or replace view yourViewName as select yourValue AS yourColumnName, yourValue AS yourColumnName2, . . N from dual where false;Let us implement the above syntax in order to create an empty view in MySQL −mysql> create or replace view empty_view as select "John Smith" AS ClientName, "US" AS ClientCountryName, false AS isMarried from dual where false; Query OK, 0 rows affected (0.20 sec)Let us check the description of the view −mysql> desc empty_view;This will produce the following output -+-------------------+-------------+------+-----+---------+-------+ | Field ...
Read MoreHow to update all the entries except a single value in a particular column using MySQL?
To update all the entries while ignoring a single value, you need to use IF().Let us first create a table −mysql> create table DemoTable736 ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(100), isMarried boolean ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable736(CustomerName, isMarried) values('Chris', 0); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable736(CustomerName, isMarried) values('Robert', 0); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable736(CustomerName, isMarried) values('David', 0); Query OK, 1 row affected (0.24 sec) mysql> insert into ...
Read More