Functions in SQL:
There are different functions in SQL which have different uses.
- Inticap() function in SQL
- Upper() Function in SQL
- Lower() Function in SQL
- Concat() Function in SQL
- Substr() Function in SQL
- instr() function in SQL
- length function in SQL
- Round() function in SQL
- Trunc() Function in SQL
- mod() function in SQL
- rpad() function in SQL
- Lpad() function in SQL
- trim() function in SQL
- ltrin() function in SQL
- rtrim() function in SQL
Initcap() function in sql
Initcap() function in SQL convert the first letter of a string to an uppercase letter.
Syntax:
initcap(string)
For example:
select initcap(firstName) from student where studentID = 100;
So the first name of a student having an id of 100, the first letter will be converted to the uppercase letter
upper() function in sql
It converts the given lowercase string to uppercase
Usage:
select upper(“how are you”);
This will Print the how are you to an upper case like, HOW ARE YOU
Syntax:
Upper(text)
text = A string that you want to convert to uppercase.
Other Example:
To covert, the studentsName in the student table to upper case letter
Select Upper(studentsName) AS UppercaseStudentsName From Customers;
Lower() function in SQL:
It converts the given uppercase string to lowercase
Syntax:
lower(text)
text = A string that you want to convert to lowercase
Usage:
Select lower(“HOW ARE YOU”)
This will convert the HOW ARE YOU to how are you
Other Example:
To convert the names of students in the Students table to lowercase we use
select lower(StudentsName) AS LowerCaseStudetnsName from students.
This function will convert all the students’ names in the students’ table to a lower case tables.
Concat() function in SQL
The Concat function in SQL is used to combine two strings together.
Syntax:
Concat(String1,string2,string3,….)
Concat() function in sql is used to combine two or more strings together
select concat(“Courseshub”,”.org”);
select concat(“I”,”am”,”learning”,”SQL”);
string1,string2,string3 = Required strings that is to be concatenated.
substr() function in sql
This substr() function is used to extract a substring from a given string
Syntax:
Substr(String, Start, length)
String: The given complete String from which you want to extract the string. Start Starting position of a string that is to be extracted. This number can be positive and can be a negative number. If the number is positive then the string will be extracted from the start and if a negative number is given then the string will be extracted from the end of the string.
Length: Length specifies how many characters are to be extracted from a given string. It is optional, but If length is not entered, the whole string will be extracted.
For example:
Select substr(“I am learning sql”,4,3) as ExtractString;
Other Example:
If we want to extract studentName from student table then we use select substr(studentName, 2,5) as extractString from student;If we extract string that start at end of the string then
select substring(“I am Learning sql”, -4, 6) as extractString;
instr() function in SQL:
instr() function in sql search for a given string and return its position.
Syntax:
Instr(Complete String, StringToBeSearched)
For Example:
If we want to search a sql in a given string “I am learning sql”
select instr(“I am learning sql”,”sql”) as Position;
This will extract the position of sql in a given string which is 15 in this case.
Parameters:
CompleteString: A string in which another string is to be searched.
StringToBeSearched: A string that is to be searched in a complete string. It will
return 0 if the stringtobesearched is not present.
Another example:
Select instr(“courseshub.org”,”org”) as Position;
This will print the position of the org in the given string.
Length() Function in SQL:
Length() function in sql is used to find the length of characters in a string
Syntax:
length(string)
Parameters:
String: Any string to calculate its length.
select length(“I am learning sql”) as length;
This will print the length of the above string is 17.
For Example:
to calculate the length of names of students in a student table we use
select length(studentName) as the length from the student;
This will calculate the length of each student’s name present in the table.
Numeric Functions in SQL:
Round function in SQL
Round() function in SQL is used to round off a number.
Syntax:
round(Value,DegitsToRoundOff)
if you want to round off two digits then we use the round function in SQL as
select round(24.234,2) as RoundedValue;This function will roundoff 5 in 22.345 and increase one digit in 4 and after rounding off
the value will be 22.35
Trunc Function in SQL Oracle:
Trunc()
SELECT TRUNC(TO_DATE('27-OCT-22','DD-MON-YY'), 'YEAR')
"New Year" FROM DUAL;Result:
New Year
———
01-JAN-22
select sysdate from dual;to show the date of the system.
sysdate = 11/20/2021
select sysdate from dual;select round(sysdate) from dual;output 11/21/2021
select round(sysdate,’DD’) from dual;
select round(sysdate,'month') from dual;12/01/2021
Trunc(date,[format)
select trunc(sysdate) from dual;output 11/20/2021
select trunc(sysdate,'DD') from dual;same result;
select trunc(sysdate,'month') from dual;11/01/20121
select trunc(To_date('11/20/2021,'Month') from dual;11/01/2021
trunc(9.6)result = 9
select trunc(1245.324,2) from dual;1245.32
Return the remainder of 18/4:
MOD() Function in SQL
SELECT MOD(18, 4);
The MOD() function returns the remainder of a number divided by another number.
Syntax:
MOD(x, y)OR
x MOD y
OR
x % yParameter Description:
x Required. A value that will be divided by y
y Required. The divisor
SELECT 18 MOD 4;Rpad() function in sql
The Rpad() function returns an expression padded with the special
character to the right side of that expression returned.
Syntax:
Rpad(expression/column_name,length,padding-expression)
Oracle SQL developer is used to implement this function
select rpad('courseshub',15,'#') from dual;in this case, courseshub is an expression that has a length of 10 characters.
Assign the padding character that we want to pad left to the courseshub
15 will be the total length of the string by total I mean that
total length = padding Expression + expression
total length = # + courseshub
+ courseshub
courseshub
5 hashes will be added to the left of the expression.
Rpad:
select rpad(‘courseshub’,15,’#’) from dual;
in this case, the hash signs will be added to the expression which is courseshub in our case.
courseshub#####
select lpad(marks,15,’$’) from student;
in this case, $ signs will be added to the marks column.
TRIM() function in SQL:
This function is used to remove spaces from a string
LTRIM() function in SQL:
The LTRIM() left trim() function is used to remove the left spaces from a string.
for example, to remove spaces from the left side of the below string.
(” I am learning SQL”)
select LTRIM(” I am learning SQL”);
RTRIM() function in SQL:
The RTRIM() means Right Trim() function in SQL is used to remove spaces from the right side
of a string.
select RTRIM("I am learning sql ") as trimmedRight;this will remove the space from the right of the string.
We will add more different functions in SQL. Like us on Facebook and share it with your friends.
