1) Name few string functions in MySQL?
Solution:
The following are string functions:
1)RIGHT():Return the specified number of characters from right
2)CONCAT():Return concatenated string
3)LOWER():Return the column data in lowercase
4)UPPER():Return the column data in lowercase
5)LENGTH() : Return the length of column data
6)LEFT():Return the specified number of characters from left
2) In MySQL DATABASE CONCAT() function can concatenate only two arguments?
Solution:
No, In MySQL database CONCAT() can concatenate any numbers of arguments.
3) what happens when you use Null Values with the Concatenation function?
Solution:
If you concatenate a null value with a character string, the result is Null.
e.g CONCAT(NAME ,NULL) results in Null
4) Display those students whose name starts and ends with ‘a’?
TABLE1: STUDENTS
Solution:
SELECT name from students where left (name,1)=’a’ and right(name,1) ='a' ;
output:
Explanation:
RIGHT() function in MySQL is used to extract a specified number of characters from the right side of a given
string. Second argument is used to decide, how many characters it should return.
5) Display those employees whose name contains an A, k, S in the starting position without using like
operator?
TABLE: EMPLOYEE_INFORMATION
Solution: Select * from employee_information where substr(name,1,1) in ('A','K','S');
output:
Explanation:
SUBSTRING() :
function in MySQL is used to derive substring from any given string . It extracts a string with a specified length,
starting from a given location in an input string. The purpose of substring is to return a specific portion of the
string.
6) Find those employees whose job do not start with 'M' or ‘C’?
TABLE: EMPLOYEE_INFORMATION
Solution:
SELECT name,job_role,deptno FROM employee_information
WHERE left (job_role,1) not in ('m','c');
output:
Explanation: Left function will give the output as letter
7) How to remove all spaces between the letters/words of a column in MySQL ?
Use below query:
UPDATE table SET col_name = REPLACE(`col_name`, ' ', '')
8) How to add Mr. before the male and Mrs. before the female based on gender column in MySQL?
Use below query:
9) How can you retrieve a portion of any column value by using a SELECT query?
SUBSTR() function is used to retrieve the portion of any column. The use of this function is explained here with
an example.
Example:
Here, the first SELECT command is used to show all the records of the Products table and the second SELECT
command is executed using SUBSTR function and that prints only the first five characters of the name field.
SELECT SUBSTR(column,1,5) FROM products;
10) How to get the first name from the full customer name ?
We can use LOCATE() function to get the position of a space in the given customer name and using left
function we can get all the characters till one previous letter than LOCATE() position.
11) How to get the last name from the full customer name ?
We can use below query. First of all we find the total length of characters present in Name column and then
subtracts the number at which space is present. The number eventually we get after subtraction can then be
considered in right function to get the number of characters from right side.
12) How to get the middle name of person in MySQL ?
We can use the substring_index function to get the first two words and then use another substring_index
function to get the last word of the two words we obtained.
13) What is the purpose of SUBSTRING function in MySQL ?
SUBSTRING() function is used to extract the characters from the given text. We need to mention two
mandatory arguments, i.e. the first one as the text from which we have to extract the characters and the
second one as from which letter onwards we want the characters. SUBSTRING() function will by default gives
the characters from starting position till the end character.
Example, SUBSTRING(“[Link]”, 5) Output is [Link]
14) In the above example how to get the output as google ?
We need to mention the third optional argument as the number of characters we need in the output.
Example, , SUBSTRING(“[Link]”, 5, 6) Output is google
15) Does TRIM function removes the spaces between the letters in the text ?
TRIM() function is used to remove the trailing and leading whitespaces from the text. It does not remove the
spaces between the letters.
16) Can we use REPLACE function to replace any characters/punctuations/bad words in the text ?
Yes. REPLACE() function is used to replace/substitute the new characters in place of existing characters. We
can even replace a space in the text using this function.
17) How to capitalize the first letter of a word in the text ?
Get the first letter. Capitalize it. Get the remaining letters from second letter onwards. Use CONCAT to join this
first letter and the remaining letters.