- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4378 Articles for MySQL
66 Views
When MySQL SUM() function got a column, having no values, as an argument then it will return NULL, rather than 0, as output. The column can be of any data type. Following the example, using a table named ‘social’ having only one column named ‘id’ with no values, will illustrate itExamplemysql> Describe Social; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | | Name | varchar(20) | YES | ... Read More
1K+ Views
It can be done by applying TRIM() function on the column along with MySQL UPDATE statement. The example below will make it more clear.ExampleSuppose, we have a table ‘Employee’ which have the prefix ‘Dept.’ with all the values of Column ‘Department’ as follows −mysql> Select * from Employee; +------+----------------+------------+----------------------+ | Id | Name | Address | Department | +------+----------------+------------+----------------------+ | 100 | Raman | Delhi | IT Dept. | | 101 | Mohan ... Read More
198 Views
MySQL TRIM() function is used to eradicate a specific suffix or prefix or both from the string. The working of TRIM() function can be understood with the help of its syntaxSyntaxTRIM([{BOTH | LEADING | TRAILING} [str_to_remove] FROM] string)Here, The argument BOTH means the prefixes from both left and right to be removed from the string.LEADING argument means that only leading prefixes to be removed.TRAILING argument means that only trailing prefixes to be removed.Str_to_remove is the argument which means the string we want to remove from the string.String argument means the string from which the prefixes have to be removed.Examplemysql> Select ... Read More
186 Views
Suppose if a table has many values having whitespaces in the columns of a table then it is wastage of space. We can use TRIM() function to remove whitespaces from all the rows and update the table too in a single query. Following the example from ‘Employee’, having whitespaces in all its rows will exhibit the concept −Examplemysql> Select * from Employee; +------+----------------------+----------------------+----------------------+ | Id | Name | Address | Department | +------+----------------------+----------------------+----------------------+ | 100 | Raman ... Read More
85 Views
Other than LTRIM() and RTRIM() functions, MySQL has TRIM() function to remove leading and trailing function both at once from a string. The use of TRIM() function can be understood from the following example of a test_trim table which has a column ‘Name’ containing the names with leading and trailing spaces.Examplemysql> Select Name, TRIM(Name)AS 'Name Without Spaces' from test_trim; +---------------+---------------------+ | Name | Name Without Spaces | +---------------+---------------------+ | Gaurav | Gaurav | | Rahul | Rahul | | Aarav | Aarav | +---------------+---------------------+ 3 rows in set (0.00 sec)
133 Views
For removing both leading and trailing spaces at once from a string by using LTRIM() and RTRIM() functions, we must have to use one function as an argument for other function. In other words, we must have to pass either LTRIM() function as an argument of RTIM() function or vice versa. It can be understood from the following example −ExampleSuppose we have a table ‘test_trim’ having a column ‘Name’ containing the values with leading and trailing spaces both −mysql> Select * from test_trim; +---------------+ | Name | +---------------+ | Gaurav | | Rahul ... Read More
321 Views
We can use LTRIM() and RTRIM functions with MySQL update clause so that the values, after removing space characters, in the table can be updated. Following examples will demonstrate it −ExampleSuppose we know that there can be some space characters in the values of ‘Name’ column of table ‘Student’ then with the help of following single query we can remove the space characters from that column’s value and also update the table −mysql> Update Student SET Name = LTRIM(Name); Query OK, 0 rows affected (0.07 sec) Rows matched: 5 Changed: 0 Warnings: 0 mysql> Update Student SET Name = ... Read More
106 Views
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
584 Views
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
227 Views
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.