b) String Functions:-The string functions helps to perform operations on character or string type of
data. Some of the string functions are discussed below.
i) length (str):-It returns the length of the given string by counting each and every character, digits,
spaces and special characters.
Question: Display the length of Informatics Practices word.
Command: select length(‘Informatics Practices’);
Output:- 21
Question: Display the length of firstname column of an employee.
Command: select length(firstname) from employee;
Output:
ii) concat(str1,str2,…..strn):- It concatenates two or more strings as a single string. If any one of
the string in the inputs is null then the resultant output is null. If input has space then the output also
contains spaces.
Question: Concatenate the below strings as a single string ‘informatics’,’practices’,’plus2’.
Command: select concat(‘Informatics’,’practices’,’plus2’);
Output: Informaticspracticesplus2
Question:Display the below strings as single string using space ‘cbse’,’board’.
Command: select concat(‘cbse’,’ ‘,’board’);
Output: cbse board
Question: Display the below strings by concatenating them ‘ip’,’board’,null,’exam’.
Command: select concat(‘ip’,’board’,null,’exam’);
Output: null
Question: Display the firstname and lastname columns by concatenating them.
Command: select concat(firstname,lastname) from employee;
Output:
iii) Lower(str) (or) Lcase(str):-It returns the given string in lower case. It changes all the characters
of the given string to lowercase.
Question: Display the string ‘Informatics Practices’ in lower case.
Command: select lcase(‘Informatics Practices’);
Output: informatics practices
Question: Display the firstname column of an employee table in lower case.
Command: select lower(firstname) from employee;
Output:
iv) Upper(str) (or) Ucase(str):-It returns the given string in upper case. It changes all the characters
of the given string to uppercase.
Question: Display the string ‘Informatics Practices’ in upper case.
Command: select ucase(‘Informatics Practices’);
Output: INFORMATICS PRACTICES
Question: Display the lastname column of an employee in upper case.
Command: select upper(lastname) from employee;
Output:
v) Left(str,n):-It returns the first n characters from the given string (or) It returns the n number of
characters from the left side of the given string.
Question: Display first 6 characters from ‘informatics’.
Command: select left(‘informatics’,6);
Output: inform
Question: Display first 3 characters from the firstname column of an employee.
Command: select left(firstname,3) from employee;
Output:
vi) Right(str,n):- It returns the last n characters from the string (or) It returns the n number of
characters from the right side of the given string.
Question: Display the last 6 characters from ‘informatics’.
Command: select right(‘informatics’,6);
Output: matics
Question: Display the last 2 characters from the lastname column of an employee.
Command: select right(lastname,2) from employee;
Output:
vii)Ltrim(Str):-It returns the given string by removing leading spaces or left hand side spaces.
Question: Display the string ‘ Informatics practices’ by removing leading spaces.
Command: select ltrim(‘ Informatics practices’);
Output: Informatics practices
Question: Display the firstname column of an employee by removing leading spaces.
Command: select ltrim(firstname) from employee;
Output:
viii)Rtrim(Str):- It returns the given string by removing trailing spaces or right hand side spaces.
Question: Display the string ‘Informatics practices ‘by removing trailing spaces.
Command: select rtim(‘Informatics practices ‘);
Output: Informatics practices
Question: Display the lastname column of an employee by removing trailing spaces.
Command: select rtim(lastname) from employee;
Output:
ix)trim(Str):- It returns the given string by removing both leading and trailing spaces. i.e., It removes
spaces from both left and right side of the string but the spaces in between won’t be removed.
Question: Display the string ‘ informatics practices ‘ by removing leading and trailing
spaces.
Command: select trim(‘ informatics practices ‘);
Output: informatics practices
Question: Display the firstname column of an employee by removing the leading and trailing spaces.
Command: select trim(firstname) from employee;
Output:
x) instr(str,substr):- It returns the first occurrence position of substring in the given string. If
substring is not available in the given string then it returns zero as an output.
Question: Display the first occurrence position of ‘form’ in ‘informatics’.
Command: Select instr (‘informatics’, ‘form’);
Output:- 3
Question: Display the first occurrence position of ‘i’ in ‘informatics’.
Command: select instr(‘informatics’,’i’);
Output: 1
Question: Display the first occurrence position of ‘far’ in ‘informatics’.
Command: select instr(‘informatics’,’far’);
Output: 0
Question: Display the occurrence position of ‘a’ in lastname column of an employee.
Command: select instr(lastname,’a’) from employee;
Output:
xi)substring(str,m,n) (or) substr(str,m,n) (or) mid(str,m,n):-This method extracts substring from
the original string. It has 3 parameters str, m, n. str is the input string, m is the position from where
we would like to extract and n is number of characters to be extracted. i.e., This function extracts n
number of characters from mth position in the specified string. Here n is optional. If n is not specified
then it returns the substring from the mth position till the end of the string. If m is negative, then it
extracts substring from back side.
Question: Display ‘form’ from ‘informatics’.
Command: select substring (‘informatics’, 3, 4);
Output:- form
Question: Display ‘matics’ from ‘informatics’.
Command: select substr(‘informatics’,6);
Output: matics
Question: Display last 2 characters of firstname column of an employee.
Command: select substr(firstname,-2) from employee;
Output:
Question: Display 4 characters from 2nd position in the last name column of an employee.
Command: select mid(lastname,2,4) from employee;
Output:
xii)ASCII(char/str):- It returns the ascii value for the specified character. If a string is passed as an
input then it returns the ascii value for the first character of the given string. If input is an empty
string then it returns 0 as an output. If input is a null then it returns null as an output.
Question: Display the ascii value of ‘D’.
Command: select ascii(‘D’);
Output:
Question: Display the ascii value of ‘informatics’.
Command: select ascii(‘informatics’);
Output:
Question: Display the ascii value of an empty string.
Command: select ascii(‘’);
Output:
Question: Display the ascii value of null.
Command: select ascii(null);
Output:
xiii) char(ascii):- It returns the character for the specified ascii value.
Question: Display the character for the ascii value 100
Command: select char(100);
Output:
Question: Display the characters for the ascii values 100,110,90.
Command: select char(100,110,90);
Output: