MySQL LTRIM() and RTRIM() functions can be used to eradicate leading and trailing spaces from a string.MySQL LTRIM() function is used to remove the leading space characters from a string. Its syntax can be as follows −SyntaxLTRIM(String)Here, String, is the string, passed as an argument, whose leading space characters are to be removed.Examplemysql> Select LTRIM(' Hello'); +--------------------+ | LTRIM(' Hello')    | +--------------------+ | Hello              | +--------------------+ 1 row in set (0.00 sec)MySQL RTRIM() function is used to remove the trailing space characters from a string. Its syntax can be as follows −SyntaxRTRIM(String)Here, String, ... Read More

For using LPAD() or RPAD() functions with the column values we need to specify the column name as the first argument of these functions. Following the example from ‘Student’ table will make it clearer −Examplemysql> Select Name, LPAD(Name, 10, '*') from student; +---------+-------------------+ | Name    | LPAD(Name, 10, '*') | +---------+-------------------+ | Gaurav  | ****Gaurav        | | Aarav   | *****Aarav        | | Harshit | ***Harshit        | | Gaurav  | ****Gaurav        | | Yashraj | ***Yashraj        | +---------+-------------------+ 5 rows in set (0.08 ... Read More

In this case, MySQL will not pad anything and truncate the characters from the original string up to the value of length provided as the argument in LPAD() or RPAD() functions.Examplemysql> Select LPAD('ABCD',3,'*'); +--------------------+ | LPAD('ABCD',3,'*') | +--------------------+ | ABC                | +--------------------+ 1 row in set (0.00 sec) mysql> Select RPAD('ABCD',3,'*'); +--------------------+ | RPAD('ABCD',3,'*') | +--------------------+ | ABC                | +--------------------+ 1 row in set (0.00 sec)We can observe from the above example that both the functions do not pad ‘*’ and truncate the original string up to the length specified i.e. 3 as the argument.

Suppose if we provide an empty string for padding on LPAD() or RPAD() functions then MySQL will return NULL as output. Following example will demonstrate it −Examplemysql> Select LPAD('ABCD',22,''); +--------------------+ | LPAD('ABCD',22,'') | +--------------------+ | NULL               | +--------------------+ 1 row in set (0.00 sec) mysql> Select RPAD('ABCD',22,''); +--------------------+ | RPAD('ABCD',22,'') | +--------------------+ | NULL               | +--------------------+ 1 row in set (0.00 sec)

For achieving this, we must have to use one of the functions as the 1st argument of the other function. In other words, either RPAD() function would be the 1st argument of LPAD() function or LPAD() function would be the 1st argument of RPAD() function. It can be understood with the help of the following exampleExamplemysql> Select RPAD(LPAD(' My name is Ram ', 23, '* '), 30, '* '); +------------------------------------------------+ | RPAD(LPAD(' My name is Ram ', 23, '* '), 30, '* ') | +------------------------------------------------+ | * * * * My name is Ram * * * *     ... Read More

MySQL has two functions namely LPAD() and RPAD() with the help of which we can pad a string with another string.LPAD() function, as the name suggests, left pads a string with another string. Following is the syntax for using it in MySQLSyntaxLPAD(original_string, @length, pad_string)Here,  original_string is the string in which we pad another string.@length is the total length of the string returned after padding.Pad_string is the string which is to be padded with original_string.Examplemysql> Select LPAD('My name is Ram', 22, '* '); +--------------------------------+ | LPAD('My name is Ram', 22, '* ') | +--------------------------------+ | * * * * My name ... Read More

In case if the number of characters to be removed exceeds the number of characters available in original string then MySQL INSERT() function will continue to remove the characters until the end of the original string.Examplemysql> Select INSERT('myteststring',3,15,'original'); +----------------------------------------+ | INSERT('myteststring',3,15,'original') | +----------------------------------------+ | myoriginal                             | +----------------------------------------+ 1 row in set (0.00 sec)

For this purpose, We need to use the name of the column as the first parameter i.e. at the place of an original string, of the INSERT() function. Following example will exhibit it −ExampleSuppose we want to add ‘/Old’ with the values of ‘year_of_admission’ column of ‘Student’ table then we need to write following query −mysql> Select INSERT(year_of_admission, 5, 0, '/Old')As 'Old Admissions' From Student; +-----------------+ | Old Admissions  | +-----------------+ | 2001/Old        | | 2010/Old        | | 2009/Old        | | 2017/Old        | | 2000/Old     ... Read More

We can use the conditions in WHERE clause while exporting the data from MySQL table to a file. It can be understood with the help of an example −ExampleSuppose we are having following data from table ‘Student_info’ −mysql> Select * from Student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Chandigarh | Literature | | 125  | Raman   | Shimla     | Computers  | | 130  | Ram     | Jhansi     | ... Read More

MySQL INSERT() function performs no insertion if the position of insertion is out of range. The position of insertion can be out of range in the case when we pass a negative or 0(zero) value or the value goes beyond the value of a total number of characters in an original string by 2. It can be understood with the help of the following example −ExampleThe query below will perform no insertion because the position of insertion is out of range i.e. a negative value.mysql> Select INSERT('Virat', -1, 5, 'Kohli'); +-------------------------------+ | INSERT('Virat', -1, 5, 'Kohli') | +-------------------------------+ | Virat ... Read More