0% found this document useful (0 votes)

15 views22 pages

Numeric and String Functions in MySQL

This document contains examples of using various string and numeric functions in MySQL such as ABS, ACOS, ASIN, ATAN, CEIL, COS, LOG, MOD, PI, POW, ROUND and more. It shows the syntax of each function, the values or results returned when different parameters are passed to the functions. Aggregate functions like AVG, COUNT, MAX, MIN, SUM are also demonstrated on sample data from a 'details' table.

Uploaded by

Vishal Jindal

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

15 views22 pages

Numeric and String Functions in MySQL

Uploaded by

Vishal Jindal

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

mysql> use laptops;

Database changed

NUMERIC FUNCTIONS
mysql> select abs(-55);
+----------+
| abs(-55) |
+----------+
| 55 |
+----------+
1 row in set (0.01 sec)

mysql> select acos(10);

+----------+
| acos(10) |
+----------+
| NULL |
+----------+
1 row in set (0.01 sec)

mysql> select acos(1);

+---------+
| acos(1) |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)

mysql> select acos(-1);

+-------------------+
| acos(-1) |
+-------------------+
| 3.141592653589793 |
+-------------------+
1 row in set (0.00 sec)

mysql> select acos(-0.7);

+------------------+
| acos(-0.7) |
+------------------+
| 2.34619382340565 |
+------------------+
1 row in set (0.00 sec)
mysql> select asin(-1);
+---------------------+
| asin(-1) |
+---------------------+
| -1.5707963267948966 |
+---------------------+
1 row in set (0.00 sec)

mysql> select asin(-10);

+-----------+
| asin(-10) |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

mysql> select atan(-10);

+---------------------+
| atan(-10) |
+---------------------+
| -1.4711276743037347 |
+---------------------+
1 row in set (0.00 sec)

mysql> select atan(2);

+--------------------+
| atan(2) |
+--------------------+
| 1.1071487177940904 |
+--------------------+
1 row in set (0.00 sec)

mysql> select atan2(10,12);

+--------------------+
| atan2(10,12) |
+--------------------+
| 0.6947382761967031 |
+--------------------+
1 row in set (0.00 sec)

mysql> select ceil(3.4);

+-----------+
| ceil(3.4) |
+-----------+
| 4 |
+-----------+
1 row in set (0.01 sec)

mysql> select cos(0.4);

+--------------------+
| cos(0.4) |
+--------------------+
| 0.9210609940028851 |
+--------------------+
1 row in set (0.00 sec)

mysql> select cos(1);

+--------------------+
| cos(1) |
+--------------------+
| 0.5403023058681398 |
+--------------------+
1 row in set (0.00 sec)

mysql> select log(10);

+-------------------+
| log(10) |
+-------------------+
| 2.302585092994046 |
+-------------------+
1 row in set (0.01 sec)

mysql> select log(1);

+--------+
| log(1) |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)

mysql> select log2(10);

+-------------------+
| log2(10) |
+-------------------+
| 3.321928094887362 |
+-------------------+
1 row in set (0.00 sec)
mysql> select log2(1);
+---------+
| log2(1) |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)

mysql> select mod(10,5);

+-----------+
| mod(10,5) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)

mysql> select mod(9,2);

+----------+
| mod(9,2) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

mysql> select pi();

+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.01 sec)

mysql> select pow(2,5);

+----------+
| pow(2,5) |
+----------+
| 32 |
+----------+
1 row in set (0.00 sec)

mysql> select cot(1);

+--------------------+
| cot(1) |
+--------------------+
| 0.6420926159343306 |
+--------------------+
1 row in set (0.01 sec)

mysql> select degrees(90);

+-------------------+
| degrees(90) |
+-------------------+
| 5156.620156177409 |
+-------------------+
1 row in set (0.01 sec)

mysql> select degrees(2);

+--------------------+
| degrees(2) |
+--------------------+
| 114.59155902616465 |
+--------------------+
1 row in set (0.00 sec)

mysql> select (63)div(33);

+---------------+
| (6*3)div(3*3) |
+---------------+
| 2 |
+---------------+
1 row in set (0.01 sec)

mysql> select floor(3.4);

+------------+
| floor(3.4) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)

mysql> select greatest("apple", "Apple");

mysql> select ln(1);

+-------+
| ln(1) |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)

mysql> select ln(10);

+-------------------+
| ln(10) |
+-------------------+
| 2.302585092994046 |
+-------------------+
1 row in set (0.00 sec)

mysql> select radians(90);

+--------------------+
| radians(90) |
+--------------------+
| 1.5707963267948966 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand();

+--------------------+
| rand() |
+--------------------+
| 0.5020185369085693 |
+--------------------+
1 row in set (0.00 sec)

mysql> select round(3.4);

+------------+
| round(3.4) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)

mysql> select round(3.4233432, 2);

+---------------------+
| round(3.4233432, 2) |
+---------------------+
| 3.42 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sign(-5);

+----------+
| sign(-5) |
+----------+
| -1 |
+----------+
1 row in set (0.01 sec)

mysql> select sin(1);

+--------------------+
| sin(1) |
+--------------------+
| 0.8414709848078965 |
+--------------------+
1 row in set (0.00 sec)

mysql> select sqrt(4);

+---------+
| sqrt(4) |
+---------+
| 2 |
+---------+
1 row in set (0.01 sec)

mysql> select tan(2);

+--------------------+
| tan(2) |
+--------------------+
| -2.185039863261519 |
+--------------------+
1 row in set (0.01 sec)
mysql> select tan(1);
+--------------------+
| tan(1) |
+--------------------+
| 1.5574077246549023 |
+--------------------+
1 row in set (0.00 sec)

mysql> select truncate(3.4233432, 2);

+------------------------+
| truncate(3.4233432, 2) |
+------------------------+
| 3.42 |
+------------------------+
1 row in set (0.00 sec)
mysql> select avg(Price) from details;
+------------+
| avg(Price) |
+------------+
| 73166.6667 |
+------------+
1 row in set (0.01 sec)

mysql> select sum(Price) from details;

+------------+
| sum(Price) |
+------------+
| 439000 |
+------------+
1 row in set (0.00 sec)

mysql> select min(Price) from details;

+------------+
| min(Price) |
+------------+
| 34000 |
+------------+
1 row in set (0.00 sec)

mysql> select max(Price) from details;

+------------+
| max(Price) |
+------------+
| 132000 |
+------------+
1 row in set (0.00 sec)
mysql> select count(*) from details;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)

mysql> select count(distinct Brand) from details;

+-----------------------+
| count(distinct Brand) |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)
STRING FUNCTIONS
mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
| 97 |
+------------+
1 row in set (0.01 sec)

mysql> select ascii('A');

+------------+
| ascii('A') |
+------------+
| 65 |
+------------+
1 row in set (0.00 sec)

//as you can see below, even if we give a string, only the first character’s ascii is given

mysql> select ascii('asdaf');

+----------------+
| ascii('asdaf') |
+----------------+
| 97 |
+----------------+
1 row in set (0.00 sec)
mysql> select char(65);
+--------------------+
| char(65) |
+--------------------+
| |
+--------------------+
1 row in set (0.00 sec)

mysql> select concat(Brand, " ", Name) from details;

//the first operator in concat_ws is the separator used while concatenating the two strings

mysql> select concat_ws(',', Brand, Name) from details;

+-----------------------------+
| concat_ws(',', Brand, Name) |
+-----------------------------+
| Lenovo,Ideapad3 |
| HP,Pavillion |
| Apple,Macbook Air |
| Apple,Macbook Pro |
| Asus,Vivobook |
| Lenovo,Slim5 |
+-----------------------------+
6 rows in set (0.00 sec)
mysql> select format(Price, 1) from details;
+------------------+
| format(Price, 1) |
+------------------+
| 34,000.0 |
| 44,000.0 |
| 92,000.0 |
| 132,000.0 |
| 70,000.0 |
| 67,000.0 |
+------------------+
6 rows in set (0.01 sec)
mysql> select format(Price, 2) from details;
+------------------+
| format(Price, 2) |
+------------------+
| 34,000.00 |
| 44,000.00 |
| 92,000.00 |
| 132,000.00 |
| 70,000.00 |
| 67,000.00 |
+------------------+
6 rows in set (0.00 sec)

mysql> select insert("Name", 2, 3, "len");

+-----------------------------+
| insert("Name", 2, 3, "len") |
+-----------------------------+
| Nlen |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select length(Name) from details;

+--------------+
| length(Name) |
+--------------+
| 8 |
| 9 |
| 11 |
| 11 |
| 8 |
| 5 |
+--------------+
6 rows in set (0.01 sec)

mysql> select locate("me", "Name");

mysql> select rtrim("Nameeee ");

+------------------------+
| rtrim("Nameeee ") |
+------------------------+
| Nameeee |
+------------------------+
1 row in set (0.00 sec)

//You can see the length after rtrim is lesser than normal, which means all the blank spaces to the right are removed

mysql> select length(rtrim("Nameeee "));

+--------------------------------+
| length(rtrim("Nameeee ")) |
+--------------------------------+
| 7 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select length("Nameeee ");

+-------------------------+
| length("Nameeee ") |
+-------------------------+
| 13 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select length(space(5));

+------------------+
| length(space(5)) |
+------------------+
| 5 |
+------------------+
1 row in set (0.00 sec)
mysql> select strcmp(Name, Brand) from details;
+---------------------+
| strcmp(Name, Brand) |
+---------------------+
| -1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---------------------+
6 rows in set (0.00 sec)

mysql> select substring_index("Apple", "p", 2);

+----------------------------------+
| substring_index("Apple", "p", 2) |
+----------------------------------+
| Ap |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index("Apple", "p", 1);

+----------------------------------+
| substring_index("Apple", "p", 1) |
+----------------------------------+
| A |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select lower(Brand) from details;

mysql> select length(" skyyy");

+---------------------+
| length(" skyyy") |
+---------------------+
| 9 |
+---------------------+
1 row in set (0.00 sec)

//ltrim also works in a similar way like rtrim

mysql> select length(ltrim(" skyyy"));

+----------------------------+
| length(ltrim(" skyyy")) |
+----------------------------+
| 5 |
+----------------------------+
1 row in set (0.01 sec)

//the trim function can be leading, trailing, or both

mysql> select trim(leading "k" from "kkangaroo");

+------------------------------------+
| trim(leading "k" from "kkangaroo") |
+------------------------------------+
| angaroo |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(both "k" from "kkangaroo");
+---------------------------------+
| trim(both "k" from "kkangaroo") |
+---------------------------------+
| angaroo |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select trim(both "o" from "ookkangaroo");
+-----------------------------------+
| trim(both "o" from "ookkangaroo") |
+-----------------------------------+
| kkangar |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select position("me" in "Name");

+--------------------------+
| position("me" in "Name") |
+--------------------------+
| 3 |
+--------------------------+
1 row in set (0.01 sec)

mysql> select left(Name, 2) from details;

+---------------+
| left(Name, 2) |
+---------------+
| Id |
| Pa |
| Ma |
| Ma |
| Vi |
| Sl |
+---------------+
6 rows in set (0.01 sec)

mysql> select right(Name, 2) from details;

+----------------+
| right(Name, 2) |
+----------------+
| d3 |
| on |
| ir |
| ro |
| ok |
| m5 |
+----------------+
6 rows in set (0.00 sec)
mysql> select replace("Lenovo", "L", "G");
+-----------------------------+
| replace("Lenovo", "L", "G") |
+-----------------------------+
| Genovo |
+-----------------------------+
1 row in set (0.01 sec)

mysql> select field("S", "A", "B", "c", "s", "r", "z");

+------------------------------------------+
| field("S", "A", "B", "c", "s", "r", "z") |
+------------------------------------------+
| 4 |
+------------------------------------------+
1 row in set (0.01 sec)

mysql> select char_length(Name) from details;

+-------------------+
| char_length(Name) |
+-------------------+
| 8 |
| 9 |
| 11 |
| 11 |
| 8 |
| 5 |
+-------------------+
6 rows in set (0.01 sec)

//if you give spaces in find_in_set it will not give the desired output

mysql> select find_in_set("S", "A, B, C, D, S, R, Z");

+-----------------------------------------+
| find_in_set("S", "A, B, C, D, S, R, Z") |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.01 sec)

mysql> select find_in_set("S", "A,B,C,D,S,R,Z");

+-----------------------------------+
| find_in_set("S", "A,B,C,D,S,R,Z") |
+-----------------------------------+
| 5 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select instr("Lenovo", "ov");
+-----------------------+
| instr("Lenovo", "ov") |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select lpad(Name, 30, "*") from details;

mysql> select rpad(Name, 30, "*") from details;

mysql> select Name, repeat(Name, 3) from details;

Date and Time function

mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2022-09-13 |
+------------+
1 row in set (0.01 sec)

mysql> select current_time();

+----------------+
| current_time() |
+----------------+
| 11:25:59 |
+----------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();

+---------------------+
| current_timestamp() |
+---------------------+
| 2022-09-13 11:26:10 |
+---------------------+
1 row in set (0.01 sec)

mysql> select now();

+---------------------+
| now() |
+---------------------+
| 2022-09-13 11:57:11 |
+---------------------+
1 row in set (0.00 sec)
mysql> select adddate('2022-08-15', 5);
+--------------------------+
| adddate('2022-08-15', 5) |
+--------------------------+
| 2022-08-20 |
+--------------------------+
1 row in set (0.01 sec)

mysql> select adddate('2022-08-15', interval 1 month);

+-----------------------------------------+
| adddate('2022-08-15', interval 1 month) |
+-----------------------------------------+
| 2022-09-15 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select adddate('2022-08-15', interval 1 week);

+----------------------------------------+
| adddate('2022-08-15', interval 1 week) |
+----------------------------------------+
| 2022-08-22 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select sec_to_time(3600);

+-------------------+
| sec_to_time(3600) |
+-------------------+
| 01:00:00 |
+-------------------+
1 row in set (0.01 sec)

mysql> select to_days('2022-08-15');

+-----------------------+
| to_days('2022-08-15') |
+-----------------------+
| 738747 |
+-----------------------+
1 row in set (0.01 sec)

mysql> select datediff('2022-08-15','2022-08-20');

+-------------------------------------+
| datediff('2022-08-15','2022-08-20') |
+-------------------------------------+
| -5 |
+-------------------------------------+
1 row in set (0.01 sec)
mysql> select date_format('2022-08-15', '%d/%m/%y');
+---------------------------------------+
| date_format('2022-08-15', '%d/%m/%y') |
+---------------------------------------+
| 15/08/22 |
+---------------------------------------+
1 row in set (0.00 sec)

Conversion functions
mysql> select convert("2022-08-15", Date) as to_date;
+------------+
| to_date |
+------------+
| 2022-08-15 |
+------------+
1 row in set (0.01 sec)

mysql> select convert("2022-08-15", datetime) as to_date;

+---------------------+
| to_date |
+---------------------+
| 2022-08-15 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select convert(201905, time) as to_time;

+----------+
| to_time |
+----------+
| 20:19:05 |
+----------+
1 row in set (0.00 sec)

mysql> select * from details;

+---------+-----------+--------+--------+---------+
| ID | Name | Brand | Price | OS |
+---------+-----------+--------+--------+---------+
| 2141101 | Ideapad3 | Lenovo | 34000 | Windows |
| 2141102 | Pavillion | HP | 44000 | Windows |
| 2141103 | Macbook | Apple | 92000 | MacOS |
| 2141104 | Macbook | Apple | 132000 | MacOS |
| 2141105 | Vivobook | Asus | 70000 | Windows |
| 2141106 | Slim5 | Lenovo | 67000 | Windows |
+---------+-----------+--------+--------+---------+
6 rows in set (0.03 sec)
mysql> select convert(price, time) as to_time from details;
+----------+
| to_time |
+----------+
| 03:40:00 |
| 04:40:00 |
| 09:20:00 |
| 13:20:00 |
| 07:00:00 |
| NULL |
+----------+
6 rows in set, 1 warning (0.00 sec)

mysql> select convert(price, char) as to_time from details;

+---------+
| to_time |
+---------+
| 34000 |
| 44000 |
| 92000 |
| 132000 |
| 70000 |
| 67000 |
+---------+
6 rows in set (0.01 sec)

mysql> select convert(price, char) as to_char from details;

+---------+
| to_char |
+---------+
| 34000 |
| 44000 |
| 92000 |
| 132000 |
| 70000 |
| 67000 |
+---------+
6 rows in set (0.00 sec)

mysql> select convert(6-1, signed) as to_signed;

+-----------+
| to_signed |
+-----------+
| 5 |
+-----------+
1 row in set (0.01 sec)
mysql> select convert(6-7, signed) as to_signed;
+-----------+
| to_signed |
+-----------+
| -1 |
+-----------+
1 row in set (0.00 sec)

mysql> select convert(6-7, unsigned) as to_signed;

+----------------------+
| to_signed |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> select convert(18446744073709551615, signed) as to_signed;

+-----------+
| to_signed |
+-----------+
| -1 |
+-----------+
1 row in set (0.00 sec)

mysql> select convert(price, decimal(10,2)) as to_decimal from details;

+------------+
| to_decimal |
+------------+
| 34000.00 |
| 44000.00 |
| 92000.00 |
| 132000.00 |
| 70000.00 |
| 67000.00 |
+------------+
6 rows in set (0.01 sec)

mysql> notee

Update MySQL Data with MySQLi and PDO
No ratings yet
Update MySQL Data with MySQLi and PDO
4 pages
PL/SQL Stored Procedures Guide
No ratings yet
PL/SQL Stored Procedures Guide
39 pages
MySQL Joins
No ratings yet
MySQL Joins
11 pages
SQL Employee Table Management Guide
No ratings yet
SQL Employee Table Management Guide
1 page
PHP Final Cae
No ratings yet
PHP Final Cae
4 pages
Limbajul PHP
No ratings yet
Limbajul PHP
4 pages
SQL Data Type Conversion and String Functions
No ratings yet
SQL Data Type Conversion and String Functions
4 pages
SQL String Functions and Data Standardization
No ratings yet
SQL String Functions and Data Standardization
4 pages
ELECT 2: Technical Elective Database Management System Instructor
No ratings yet
ELECT 2: Technical Elective Database Management System Instructor
14 pages
SQL Built-in Functions Overview
No ratings yet
SQL Built-in Functions Overview
10 pages
SQL Use Cases: Signed vs. Unsigned Values
No ratings yet
SQL Use Cases: Signed vs. Unsigned Values
76 pages
SQL Comments: Types and Usage Guide
No ratings yet
SQL Comments: Types and Usage Guide
5 pages
POS Business Classifications Query
No ratings yet
POS Business Classifications Query
2 pages
Understanding SQL DUAL Table & Functions
No ratings yet
Understanding SQL DUAL Table & Functions
64 pages
Understanding SQL Cartesian Product
No ratings yet
Understanding SQL Cartesian Product
9 pages
Single Vs Double Quotes - Qlikview - How and When To Use
No ratings yet
Single Vs Double Quotes - Qlikview - How and When To Use
3 pages
Database Column Specifications Overview
No ratings yet
Database Column Specifications Overview
4 pages
PL/SQL Cursors: Implicit & Explicit Guide
No ratings yet
PL/SQL Cursors: Implicit & Explicit Guide
3 pages
Understanding PL/SQL Cursors Explained
No ratings yet
Understanding PL/SQL Cursors Explained
18 pages
ServiceNow Glide Scripting Overview
No ratings yet
ServiceNow Glide Scripting Overview
141 pages
Understanding Explicit Cursors in PL/SQL
No ratings yet
Understanding Explicit Cursors in PL/SQL
30 pages
PL/SQL Records Overview
No ratings yet
PL/SQL Records Overview
3 pages
MySQL String and Date-Time Operations
No ratings yet
MySQL String and Date-Time Operations
4 pages
SQL 1 & 2
No ratings yet
SQL 1 & 2
1 page
MySQL Single Row Functions Guide
No ratings yet
MySQL Single Row Functions Guide
14 pages
SQL Clauses and Functions Overview
No ratings yet
SQL Clauses and Functions Overview
58 pages
SQL Sales Table Operations Guide
No ratings yet
SQL Sales Table Operations Guide
7 pages
SQL Functions Overview: String & Math
No ratings yet
SQL Functions Overview: String & Math
37 pages
DBMS Practical 7th
No ratings yet
DBMS Practical 7th
3 pages
Understanding Cursors in Oracle PL/SQL
No ratings yet
Understanding Cursors in Oracle PL/SQL
1 page
SQL and PLSQL Programs 250407 205900
No ratings yet
SQL and PLSQL Programs 250407 205900
2 pages
Oracle SQL Joins Explained
No ratings yet
Oracle SQL Joins Explained
11 pages
Candidate Key vs Primary Key Explained
No ratings yet
Candidate Key vs Primary Key Explained
25 pages
Source Code Exp 1
No ratings yet
Source Code Exp 1
4 pages
EventRequest Table Schema Overview
No ratings yet
EventRequest Table Schema Overview
2 pages
Understanding SQL Column Aliases and Literals
No ratings yet
Understanding SQL Column Aliases and Literals
3 pages
SQL CREATE TABLE Syntax Explained
No ratings yet
SQL CREATE TABLE Syntax Explained
10 pages
MQLMySQL Library Reference Guide
No ratings yet
MQLMySQL Library Reference Guide
6 pages
Contoh Stored Procedure SQL Barang
No ratings yet
Contoh Stored Procedure SQL Barang
3 pages
Trigger Table Schema Definition
No ratings yet
Trigger Table Schema Definition
1 page
MySQL User Table Structure Overview
No ratings yet
MySQL User Table Structure Overview
3 pages
SAP Clearing Interface Implementation Guide
No ratings yet
SAP Clearing Interface Implementation Guide
53 pages
Cash 4 Windows Data Overview
No ratings yet
Cash 4 Windows Data Overview
1 page
Essential SQL Commands Overview
No ratings yet
Essential SQL Commands Overview
8 pages
SQL Injection
No ratings yet
SQL Injection
9 pages
Oracle Apps - Updating Price List Lines - API Approach
No ratings yet
Oracle Apps - Updating Price List Lines - API Approach
9 pages
Conversion Function 02-05-25
No ratings yet
Conversion Function 02-05-25
5 pages
String Functions in MySQL Lab Guide
No ratings yet
String Functions in MySQL Lab Guide
7 pages
SQL String Functions Explained
No ratings yet
SQL String Functions Explained
3 pages
SQL Aggregate Functions Explained
No ratings yet
SQL Aggregate Functions Explained
2 pages
Database Management System SQL Examples
No ratings yet
Database Management System SQL Examples
6 pages
SQL Queries for Fruit Table Management
No ratings yet
SQL Queries for Fruit Table Management
2 pages
SQL Procedures for Container Booking and Math Functions
No ratings yet
SQL Procedures for Container Booking and Math Functions
4 pages
SQL Queries for Fruit Table Management
No ratings yet
SQL Queries for Fruit Table Management
2 pages
SQL Transaction Control Language Guide
No ratings yet
SQL Transaction Control Language Guide
10 pages
Control Levels for Internal Table Grouping
No ratings yet
Control Levels for Internal Table Grouping
4 pages
PeopleSoft Voucher and Finance Tables
No ratings yet
PeopleSoft Voucher and Finance Tables
4 pages
Copying SQL Tables Between Users
No ratings yet
Copying SQL Tables Between Users
3 pages
SQL Manual Screenshot Style
No ratings yet
SQL Manual Screenshot Style
1 page
SQL Functions for Class 12 Students
No ratings yet
SQL Functions for Class 12 Students
14 pages
Relational Algebra and SQL Overview
No ratings yet
Relational Algebra and SQL Overview
17 pages
Key Statistical Ideas of the Last 50 Years
No ratings yet
Key Statistical Ideas of the Last 50 Years
19 pages
3 Transaction Processing & Accounting Transaction Cycles
No ratings yet
3 Transaction Processing & Accounting Transaction Cycles
15 pages
Assignment Problem in Quantitative Methods
No ratings yet
Assignment Problem in Quantitative Methods
3 pages
LG Wireless Rear Speakers Kit Manual
No ratings yet
LG Wireless Rear Speakers Kit Manual
2 pages
Software Developer Profile: Shakib Ahmed
No ratings yet
Software Developer Profile: Shakib Ahmed
3 pages
Array Search and Sorting Algorithms
No ratings yet
Array Search and Sorting Algorithms
15 pages
Python Problem Solving Exercises
No ratings yet
Python Problem Solving Exercises
21 pages
NMOS Design Rules and Layout Guide
No ratings yet
NMOS Design Rules and Layout Guide
53 pages
Little Man Computer Instruction Guide
No ratings yet
Little Man Computer Instruction Guide
17 pages
Plastic Flow and Interfacial Bonding Behaviors of Embedded 2025 Chinese Jou
No ratings yet
Plastic Flow and Interfacial Bonding Behaviors of Embedded 2025 Chinese Jou
12 pages
Generating Runtime Dumps in SAP HANA
No ratings yet
Generating Runtime Dumps in SAP HANA
5 pages
Python Programs for Class 11 Practical
100% (1)
Python Programs for Class 11 Practical
17 pages
The ECDIS Solution: Chartpilot 1100
No ratings yet
The ECDIS Solution: Chartpilot 1100
8 pages
OOP Practice Programs in Python
No ratings yet
OOP Practice Programs in Python
10 pages
3GPP TS 36.213 V13.14.0 Overview
No ratings yet
3GPP TS 36.213 V13.14.0 Overview
38 pages
Mark Vie
100% (2)
Mark Vie
4 pages
42 Essential SOLID Interview Questions
No ratings yet
42 Essential SOLID Interview Questions
1 page
Secure Multipath Routing in MANETs
No ratings yet
Secure Multipath Routing in MANETs
21 pages
Senior Java Full Stack Developer Role
No ratings yet
Senior Java Full Stack Developer Role
2 pages
IoT Digital Thermometer Block Diagram
No ratings yet
IoT Digital Thermometer Block Diagram
10 pages
2D Graphics Basics: Line Drawing & Clipping
No ratings yet
2D Graphics Basics: Line Drawing & Clipping
27 pages
Musical Siren Lab Project Report
100% (3)
Musical Siren Lab Project Report
8 pages
Xiaomi 12 Device Capability Overview
No ratings yet
Xiaomi 12 Device Capability Overview
2 pages
Binary Adder Circuit Overview
No ratings yet
Binary Adder Circuit Overview
34 pages
LG LED TV Owner's Manual
No ratings yet
LG LED TV Owner's Manual
102 pages
3D Frame Design Procedure in STAAD.PRO
No ratings yet
3D Frame Design Procedure in STAAD.PRO
1 page
Multi-Active Satellite Implementation Guide
No ratings yet
Multi-Active Satellite Implementation Guide
4 pages
WonderJourney: 3D Scene Generation
No ratings yet
WonderJourney: 3D Scene Generation
17 pages
SICOM3024 Industrial Ethernet Switch
No ratings yet
SICOM3024 Industrial Ethernet Switch
8 pages

Numeric and String Functions in MySQL

Uploaded by

Numeric and String Functions in MySQL

Uploaded by

mysql> use laptops;

mysql> select acos(10);

mysql> select acos(1);

mysql> select acos(-1);

mysql> select acos(-0.7);

mysql> select asin(-10);

mysql> select atan(-10);

mysql> select atan(2);

mysql> select atan2(10,12);

mysql> select ceil(3.4);

mysql> select cos(0.4);

mysql> select cos(1);

mysql> select log(10);

mysql> select log(1);

mysql> select log2(10);

mysql> select mod(10,5);

mysql> select mod(9,2);

mysql> select pi();

mysql> select pow(2,5);

mysql> select cot(1);

mysql> select degrees(90);

mysql> select degrees(2);

mysql> select (6*3)div(3*3);

mysql> select floor(3.4);

mysql> select greatest("apple", "Apple");

mysql> select ln(1);

mysql> select ln(10);

mysql> select radians(90);

mysql> select rand();

mysql> select round(3.4);

mysql> select round(3.4233432, 2);

mysql> select sign(-5);

mysql> select sin(1);

mysql> select sqrt(4);

mysql> select tan(2);

mysql> select truncate(3.4233432, 2);

mysql> select sum(Price) from details;

mysql> select min(Price) from details;

mysql> select max(Price) from details;

mysql> select count(distinct Brand) from details;

mysql> select ascii('A');

mysql> select ascii('asdaf');

mysql> select concat(Brand, " ", Name) from details;

mysql> select concat_ws(',', Brand, Name) from details;

mysql> select insert("Name", 2, 3, "len");

mysql> select length(Name) from details;

mysql> select locate("me", "Name");

mysql> select rtrim("Nameeee ");

mysql> select length(rtrim("Nameeee "));

mysql> select length("Nameeee ");

mysql> select length(space(5));

mysql> select substring_index("Apple", "p", 2);

mysql> select substring_index("Apple", "p", 1);

mysql> select lower(Brand) from details;

mysql> select length(" skyyy");

//ltrim also works in a similar way like rtrim

mysql> select length(ltrim(" skyyy"));

//the trim function can be leading, trailing, or both

mysql> select trim(leading "k" from "kkangaroo");

mysql> select position("me" in "Name");

mysql> select left(Name, 2) from details;

mysql> select right(Name, 2) from details;

mysql> select field("S", "A", "B", "c", "s", "r", "z");

mysql> select char_length(Name) from details;

mysql> select find_in_set("S", "A, B, C, D, S, R, Z");

mysql> select find_in_set("S", "A,B,C,D,S,R,Z");

mysql> select lpad(Name, 30, "*") from details;

mysql> select rpad(Name, 30, "*") from details;

mysql> select Name, repeat(Name, 3) from details;

Date and Time function

mysql> select current_time();

mysql> select current_timestamp();

mysql> select now();

mysql> select adddate('2022-08-15', interval 1 month);

mysql> select adddate('2022-08-15', interval 1 week);

mysql> select sec_to_time(3600);

mysql> select (63)div(33);