Functions in SQL

By | June 14, 2022

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 % y

Parameter 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.

Leave a Reply

Your email address will not be published.