Scribd
Upload
17 views36 pages

MySQL Library Functions 2025-26 String, Numerical

The document provides an overview of MySQL library functions, categorizing them into string functions, mathematical functions, and date/time functions. It includes examples of SQL commands for creating a product table and inserting values, as well as detailed explanations of various functions such as CHAR(), LENGTH(), CONCAT(), and others. Additionally, it describes the use of mathematical functions like MOD(), POWER(), and date/time functions like CURDATE() and NOW().

Uploaded by

gaurav.gupta.061007
Copyright
© © All Rights Reserved
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
17 views36 pages

MySQL Library Functions 2025-26 String, Numerical

The document provides an overview of MySQL library functions, categorizing them into string functions, mathematical functions, and date/time functions. It includes examples of SQL commands for creating a product table and inserting values, as well as detailed explanations of various functions such as CHAR(), LENGTH(), CONCAT(), and others. Additionally, it describes the use of mathematical functions like MOD(), POWER(), and date/time functions like CURDATE() and NOW().

Uploaded by

gaurav.gupta.061007
Copyright
© © All Rights Reserved
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
You are on page 1/ 36

MYSQL Library Functions

CLASS XII Informatics Practices

Vijay chawla, PGT(CS)


KV Pitampura, Delhi
MySQL Functions
 A function is a special type of predefined command set that
performs some operation and returns a single value. The input
given to the functions are known as parameters or arguments.
Categories of MySQL Function
i) String Functions
String functions are used to manipulate the text string in
many ways.
ii) Mathematical Functions / Numeric Functions
The numeric functions are those functions that accept
numeric values as input parameters (arguments) and returns
numeric values after execution of the query
iii) Date and Time Functions
Date and Time functions operate on values of the DATE
data type.
READY TO RUN CODE
create table Product
(
Pno integer(6) primary key,
Pname varchar(20) Not Null,
DOP date,
Company varchar(20),
Price decimal(7,3),
Qty varchar(30)
);
Insert Into Product Values(101,'Router','2003-12-09',"TitBit",3599.99,100);
Insert Into Product Values(102,'Switch','2005-10-06',"Cosmos",3890.89,50);
Insert Into Product Values(103,'RAM','2004-01-01',"Universal",2899.99,60);
Insert Into Product Values(104,'WebCam','2004-08-24',"Starlite",1950.49,20);
Insert Into Product Values(105,'Memory Card','2004-07-03',"PCWorks",295.00,200);
Insert Into Product(Pno,Pname,DOP) Values(106,'Head Phone','2003-02-16');
Insert Into Product Values(107,'Bluetooth Headset','2005-05-19',"Cosmos",1190.00,10);
Insert Into Product Values(108,'Speaker','2004-07-10',"StarMark",1659.89,25);
STRING FUNCTIONS- char( )
Returns the character for each integer
STRING FUNCTIONS passed.
i. Char( )
ii. Length( )
mysql> SELECT CHAR(66,65,71);
iii.Concat( )
iv.Lower( )/Lcase( ) OUTPUT
v. Upper( )/Ucase( ) BAG
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim() NOTE:
viii.Left( ) / Right( ) ASCII (American Standard Code for Informaton Interchange.
ix.Instr( ) Eg. A=65,B=66…..and so on
a= 97,b=98…..and so on
STRING FUNCTIONS- Length( )
STRING FUNCTIONS Returns
the length
i. Char( ) of the
parameter
ii.Length( ) (string)
iii.Concat( ) passed to
it.
iv.Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim() NOTE:
Length
viii.Left( ) / Right( ) considers
spaces and
ix.Instr( ) special
characters
as well.
STRING FUNCTIONS- Concat ( )
Returns the concatenated (joined) strings
STRING FUNCTIONS
i. Char( )
ii. Length( )
iii.Concat( )
iv.Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim()
viii.Left( ) / Right( )
ix.Instr( )
STRING FUNCTIONS- lower( ) / lcase( )
Returns the input parameter in lowercase.
STRING FUNCTIONS
i. Char( )
ii. Length( )
iii.Concat( )
iv.Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim()
viii.Left( ) / Right( )
ix.Instr( )
STRING FUNCTIONS- UPPER( ) / UCASE( )
Returns the input parameter in UPPERCASE (CAPITAL LETTERS)
STRING FUNCTIONS
i. Char( )
ii. Length( )
iii.Concat( )
iv.Lower( )/Lcase( )
v.Upper()/Ucase()
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim()
viii.Left( ) / Right( )
ix.Instr( )
STRING FUNCTIONS- Substring( ) / Substr() / Mid()
Returns the substring (part) of a string
STRING FUNCTIONS SYNTAX:
MID(“string”, starting Index number, number of characters to be extracted)
i. Char( ) SUBSTR(“string”, starting Index number, number of characters to be extracted)
ii. Length( ) SUBSTRING(“string”, starting Index number, number of characters to be extracted)
iii. Concat( ) NOTE: Index no. in MySQL for a string starts from 1. Any thing written
iv. Lower( )/Lcase( ) Index no. inside ‘ ’ or “ ” is the string. The output produced is the substring.
v. Upper( )/Ucase( ) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

vi.Substring( )/ W e l c o m e t o I n d i a ! ! !
Substr( )/ Characters
Mid( )
vii.Trim() , LTrim() ,

characters to e extracted
RTrim()

Here, 12 is the no. of


viii.Left( ) / Right( )
ix. Instr( )
STRING FUNCTIONS- Substring( ) / Substr() / Mid()
STRING
FUNCTIONS
i. Char( )
ii. Length( )
iii. Concat( )
iv. Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( )/
Mid( ) NOTE:
vii.Trim() , LTrim() , Substring() /
RTrim() substr() / mid()
Represent the
viii.Left( ) / Right( ) same function
ix. Instr( )
STRING FUNCTIONS- Trim(), LTrim( ), Rtrim()
Trim() : Removes both leading(from the beginning) and
STRING FUNCTIONS trailing (at the end) spaces.
i. Char( ) LTrim() : Removes leading(from the beginning) spaces.
ii. Length( ) RTrim() : Removes trailing (at the end) spaces.
iii.Concat( )
iv.Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( ) /Mid( )

vii.Trim() ,
LTrim() , RTrim()
viii.Left( ) / Right( )
ix.Instr( )
STRING FUNCTIONS- Left( ) / Right( )
STRING FUNCTIONS
Left() : Returns the extracted characters from left
(beginning) side .
i. Char( )
Right() : Returns the extracted characters from right
ii. Length( ) (trailing) side.
iii.Concat( ) SYNTAX: Left(“<String>”,<n>)
iv.Lower( )/Lcase( ) Right(“<String>”,<n>)
v. Upper( )/Ucase( ) Where, n is the no. of characters to be extracted.
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim()

viii.Left( ) / Right( )
ix.Instr( )
STRING FUNCTIONS- Left( ) / Right( )
STRING
FUNCTIONS
i. Char( )
ii. Length( )
iii.Concat( )
iv.Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim()

viii.Left( ) / Right( )
ix.Instr( )
STRING FUNCTIONS- Instr( )
STRING INSTR() : Returns the index of the first
FUNCTIONS occurrence of substring
i. Char( )
ii. Length( )
iii.Concat( )
iv.Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim()
viii.Left( ) / Right( )
ix.Instr( )
STRING FUNCTIONS- Instr( )
STRING
FUNCTIONS
i. Char( )
ii. Length( )
iii.Concat( )
iv.Lower( )/Lcase( )
v. Upper( )/Ucase( )
vi.Substring( )/
Substr( ) /Mid( )
vii.Trim() , LTrim() ,
RTrim()
viii.Left( ) / Right( )
ix.Instr( )
MATHEMATICAL
FUNCTIONS
MATHEMATICAL FUNCTIONS- Mod( ) or %
Numeric Functions

i. Mod( ) or %
ii. Power( ) /Pow( )
iii. Sqrt( )
iv. Sign( )
v. Round( )
vi. Truncate()
Returns remainder of one
expression by dividing by
another expression.
MATHEMATICAL FUNCTIONS- POWER( ) / POW()
Numeric Functions Returns
i. Mod( ) or %
the value
of one
ii.Power( ) / expression
raised to
Pow() the power
iii. Sqrt( ) of another
expression.
iv. Sign( )
v. Round( )
vi. Truncate()
MATHEMATICAL FUNCTIONS- SQRT()
Numeric Functions Returns
i. Mod( ) or % the non-
ii. Power( ) / Pow() negative
square
iii.Sqrt( ) root of
iv. Sign( ) numeric
expression.
v. Round( )
vi. Truncate()
MATHEMATICAL FUNCTIONS- ROUND( )
Numeric Functions
i. Mod( ) or %
ii. Power( ) / Pow()
iii. Sqrt( )
iv. Sign( )

v.Round( )
vi. Truncate( )
Returns numeric
expression rounded to
an integer. It can be
used to round an
expression to a number
of decimal points.
MATHEMATICAL FUNCTIONS- TRUNCATE( )
Numeric Functions
i. Mod( ) or %
ii. Power( ) / Pow()
iii. Sqrt( )
iv. Sign( )
v. Round( )
vi.Truncate()

Expression 1
Expression 2
Returns numeric
expression1 truncated
to expression2
decimal places. If
expression2 is 0, then
the result will have no
decimal point.
MATHEMATICAL FUNCTIONS- ROUND( ) Vs TRUNCATE( )
DATE AND TIME
FUNCTIONS
DATE and TIME FUNCTIONS- CurDate( )
III.DATE and TIME Returns the current date
FUNCTIONS
i. CurDate( ) /
Current_Date( )
/ Current_Date
ii. Date( )
iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIME FUNCTIONS- DATE( )
III.DATE and TIME Returns the Date part of a date or
FUNCTIONS date time expression
i. CurDate( )/
Current_Date( ) /
Current_Date

ii.Date( )
iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIMEReturns
FUNCTIONS- MONTH( )
the month from the date passed as
III.DATE and TIME argument.
FUNCTIONS
i. CurDate( )/
Current_Date( ) /
Current_Date
ii. Date( )

iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIMEReturns
FUNCTIONS- YEAR( )
the year from the Date argument
III.DATE and TIME passed.
FUNCTIONS
i. CurDate( )/
Current_Date( ) /
Current_Date
ii. Date( )
iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIME FUNCTIONS- DAYNAME( )
DATE/TIME FUNCTIONS Returns the name of the weekday.
i. CurDate()/ Current_Date()
/ Current_Date
ii. Date( )
iii.Month( )
iv.Year( )
v.Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIME FUNCTIONS- DAYOFMONTH()
DATE/TIME FUNCTIONS Returns the Day of the Month (1-31)
i. CurDate( )/
Current_Date( ) /
Current_Date
ii. Date( )
iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIME FUNCTIONS- DAYOFWEEK( )
III.DATE and TIME Returns the Day of the Week(1-7)
FUNCTIONS
i. CurDate( )/
Current_Date( ) /
Current_Date
ii. Date( )
iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIME FUNCTIONS- DAYOFYEAR( )
III.DATE and TIME Returns the Day of the Year.
FUNCTIONS
NOTE:
i. CurDate( )/ A year has
Current_Date( ) / 365/366
days.
Current_Date
ii. Date( )
iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x. Sysdate( )
DATE and TIME FUNCTIONS-
NOW( ) and SYSDATE( )
III.DATE and TIME Now() : Returns the time at which the function executes

FUNCTIONS Sysdate() : Returns the current date and time


i. CurDate( )/
Current_Date( ) /
Current_Date
ii. Date( )
iii.Month( )
iv.Year( )
v. Dayname( )
vi.DayofMonth( )
vii.DayofWeek( )
viii.DayofYear( )
ix.Now( )
x.Sysdate( )
Stay safe. Stay aware. Stay healthy. Stay alert.

You might also like