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
MySQL Articles
Page 107 of 355
Count no. of characters and words in a string in PL/SQL
We are given a string of any length and the task is to calculate the count of characters and words in a string using PL/SQL.PL/SQL is a combination of SQL along with the procedural features of programming languages.It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.In PL/SQL block, we have DECLARE block which is used to declare the variables used in programming and we have BEGIN block where we write the logic for the ...
Read MoreConvert distance from km to meters and centimeters in PL/SQL
The task is to convert the distance from kilometers to meters and centimeters in PL/SQL.PL/SQL is the extension of SQL which combines the Data manipulation of SQL with the working of procedural language.According to the problem we should have distance in kilometers whose value we have to convert in meters and centimeters.As per the conversion rule −1km = 1000 meters1km = 100000 centimetersAccording to this conversion rule we want the distance to be converted by a logic in PL/SQL.ExampleInput: kilometer = 10 Output: meter = 10000 Centimeter = 1000000 Input: kilometer = 9 Output: meter = 9000 Centimeter ...
Read MoreMySQL query to remove numbers after hyphen in a VARCHAR string with numbers
For this, use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable2040 -> ( -> StudentCode varchar(20) -> ); Query OK, 0 rows affected (0.85 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2040 values('John-232'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2040 values('Carol-901'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable2040 values('David-987'); Query OK, 1 row affected (0.21 sec)Display all records from the table using select statement −mysql> select *from DemoTable2040;This will produce the following output −+-------------+ | StudentCode | +-------------+ | ...
Read MoreDisplaying only a list of records in ASC order with MySQL
To display a list of records in a specific order, you need to set conditions and use ORDER BY. For this, use ORDER BY CASE statement. Let us first create a table −mysql> create table DemoTable2039 -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2039 values('John Doe'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable2039 values('John Smith'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable2039 values('Chris Brown'); Query OK, 1 row affected ...
Read MoreMySQL query to return TRUE for rows having positive value?
To return TRUE for positive values and FALSE for negative, use MySQL IF(). Let us first create a table −mysql> create table DemoTable2038 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value int -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2038(Value) values(57); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable2038(Value) values(-100);; Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2038(Value) values(-78); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable2038(Value) ...
Read MoreFetch the first letter of a column value and insert it in another column with MySQL
For this, use the concept of LEFT() function. Let us first create a table −mysql> create table DemoTable2036 -> ( -> FirstLetter varchar(20), -> Title varchar(20) -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2036(Title) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2036(Title) values('John'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2036(Title) values('Adam'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable2036;This will produce the ...
Read MoreValidate Date in MySQL using a custom function
Let us create a custom function to validate date in MySQL −mysql> set global log_bin_trust_function_creators=1; Query OK, 0 rows affected (0.03 sec) mysql> delimiter // mysql> create function isValidDate(actualDate varchar(255)) returns int -> begin -> declare flag int; -> if (select length(date(actualDate)) IS NOT NULL ) then -> set flag = 1; -> else -> set flag = 0; -> end if; -> return flag; -> end -> // Query OK, 0 rows affected (0.11 sec) mysql> delimiter ;Case 1 −When parameter is null value i.e. the date to be ...
Read MoreDisplay TRUE FALSE records as 0 1 in MySQL
Set the column as BOOLEAN to display 0 and 1 values. Let us create a table −mysql> create table DemoTable2035 -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> isMarried boolean, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2035(Name, isMarried) values('Chris', true); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2035(Name, isMarried) values('David', false); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable2035(Name, isMarried) values('Bob', true); Query OK, 1 ...
Read MoreImplement Dynamic SQL query inside a MySQL stored procedure?
For dynamic SQL query in a stored procedure, use the concept of PREPARE STATEMENT. Let us first create a table −mysql> create table DemoTable2033 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2033(Name) values('Chris'); Query OK, 1 row affected (0.85 sec) mysql> insert into DemoTable2033(Name) values('Bob'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable2033(Name) values('David'); Query OK, 1 row affected (0.24 sec) mysql> insert into ...
Read MoreCalculating byte values to megabyte (MB) in MySQL?
Here, we are taking BIGINT type, since it takes 8 byte signed integer. Let us first create a table with column as BIGINT type −mysql> create table DemoTable2031 -> ( -> ByteValue bigint -> ); Query OK, 0 rows affected (1.17 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2031 values(1048576); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2031 values(1073741824); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable2031;This will produce the following output −+------------+ | ByteValue | ...
Read More