Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 117 of 355
How can we split an IP Address into four respective octets by using MySQL SUBSTRING_INDEX() function?
Suppose we have a table named ‘ipaddress’ which contains the IP addresses as its values in column ‘IP’ as follows −mysql> Select * from ipaddress; +-----------------+ | ip | +-----------------+ | 192.128.0.5 | | 255.255.255.255 | | 192.0.255.255 | | 192.0.1.5 | +-----------------+ 4 rows in set (0.10 sec)Now with the help of SUBSTRING_INDEX() function in the following query, we can divide the IP address in four octets −mysql> Select IP, SUBSTRING_INDEX(ip, '.', 1)AS '1st Part', -> SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1)AS '2nd Part', ...
Read MoreHow can I check the status of MySQL Server?
With the help of ‘mysqladmin’ along with ‘status’ option program we would be able to check the status of MySQL server. It can be used as follows on command line −C:\mysql\bin>mysqladmin -u root status Uptime: 3865 Threads: 1 Questions: 50 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 102 Queries per second avg: 0.012
Read MoreHow can we analyze the tables of a particular database from MySQL Server command line?
We need to use ‘mysqlcheck’ client program along with –analyze option to analyze the tables of a particular database. Its syntax would be as follows −Mysqlcheck – u root –analyze db_nameExampleThe following command will analyze the tables of database ‘query’ −C:\mysql\bin>mysqlcheck -u root --analyze query query.cars OK query.copy_cars OK query.countries Table is already up to date query.customers ...
Read MoreHow can we find the duplicate values available in a MySQL table by using JOINS?
Suppose we have the following table named ‘stock_item’ in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ as shown in the table.mysql> Select * from stock_item; +------------+----------+ | item_name |quantity | +------------+----------+ | Calculator | 89 | | Notebooks | 40 | | Pencil | 40 | | Pens | 32 | | Shirts | 29 | | Shoes | ...
Read MoreHow can I use SPACE() function with MySQL WHERE clause?
In this case, SPACE() function would add white spaces depending upon the condition given in WHERE clause. The following example from student table will demonstrate it.Examplemysql> Select Id,Name,Space(5) from student WHERE Name='Harshit'; +------+---------+----------+ | Id | Name | Space(5) | +------+---------+----------+ | 15 | Harshit | | +------+---------+----------+ 1 row in set (0.00 sec)
Read MoreHow can we reverse a MySQL string connected by the dash?
MySQL has function name REVERSE() with the help of which we can reverse the string. But suppose if we want to reverse the string connected by dash then by using REVERSE() function will not give appropriate result as shown in the following example:mysql> Select REVERSE('AB-CD-EF'); +---------------------+ | REVERSE('AB-CD-EF') | +---------------------+ | FE-DC-BA | +---------------------+ 1 row in set (0.00 sec)The appropriate result would be ‘EF-CD-AB’ and for getting such output we can use SUBSTRING_INDEX() function along with Instr() function. It is demonstrated as follows:mysql> Select CONCAT(SUBSTRING_INDEX('AB-CD-EF', '-', -1), '-', substr('AB-CD-EF', instr('AB-CD-EF', "-")+1, instr('AB-CD-EF', "-")), LEFT('AB-CD-EF', ...
Read MoreHow can we write MySQL query for inner joins with the help of Comma operator?
Writing inner joins with the help of comma operator is the most basic way to combine two tables. As we know that we can also write inner join by using keyword INNER JOIN or synonyms like JOIN. To form an inner join we need to specify a particular condition which is known as join-predicate and while writing inner joins using the comma operator, we use WHERE clause, the only way, to specify the join condition. To understand it, we are taking the example of two tables named tbl_1 and tbl_2 which are having following data:mysql> Select * from tbl_1; +----+--------+ ...
Read MoreHow can we use MySQL REVERSE() function on column's data along with WHERE clause?
MySQL REVERSE() function can have the column name as an argument to invert its value. If we want to apply some condition/s then it can be used along with WHERE clause as follows:Examplemysql> Select Name, REVERSE(Name) from Student; +---------+---------------+ | Name | REVERSE(Name) | +---------+---------------+ | Aarav | varaA | | Gaurav | varuaG | | Gaurav | varuaG | | Harshit | tihsraH | | Yashraj | jarhsaY | +---------+---------------+ 5 rows in set (0.00 sec)The above query inverts the values ...
Read MoreIn MySQL, how can we get the number code of a particular character?
With the help of MySQL string function ASCII(), we can get the number code of a particular character. Its syntax is ASCII(str) where, str, the argument of ASCII() function, is the string whose ASCII value of the first character to be retrieved.It will return the number code the left the most character i.e. first character of the string given as argument.Examplemysql> Select ASCII('T'); +------------+ | ASCII('T') | +------------+ | 84 | +------------+ 1 row in set (0.01 sec) mysql> Select ASCII('t'); +------------+ | ASCII('t') | +------------+ | 116 | +------------+ 1 row ...
Read MoreWhat MySQL returns, if the length of the original string is greater than the length specified as an argument in LPAD() or RPAD() functions?
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.
Read More