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.
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
select upper(“how are you”);
This will Print the how are you to an upper case like, HOW ARE YOU
text = A string that you want to convert to uppercase.
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
text = A string that you want to convert to lowercase
Select lower(“HOW ARE YOU”)
This will convert the HOW ARE YOU to how are you
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.
Concat() function in sql is used to combine two or more strings together
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
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.
Select substr(“I am learning sql”,4,3) as ExtractString;
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.
Instr(Complete String, StringToBeSearched)
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.
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.
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
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.
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.
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:
SELECT TRUNC(TO_DATE('27-OCT-22','DD-MON-YY'), 'YEAR')
"New Year" FROM DUAL;
select sysdate from dual;
to show the date of the system.
sysdate = 11/20/2021
select sysdate from dual;
select round(sysdate) from dual;
select round(sysdate,’DD’) from dual;
select round(sysdate,'month') from dual;
select trunc(sysdate) from dual;
select trunc(sysdate,'DD') from dual;
select trunc(sysdate,'month') from dual;
select trunc(To_date('11/20/2021,'Month') from dual;
result = 9
select trunc(1245.324,2) from dual;
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.
x MOD y
x % y
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.
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
5 hashes will be added to the left of the expression.
select rpad(‘courseshub’,15,’#’) from dual;
in this case, the hash signs will be added to the expression which is courseshub in our case.
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.