Found 4219 Articles for MySQLi

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)

MySQL SPACE() function is used to add white spaces between two strings. The argument passed in SPACE() function is an integer which specifies how many white spaces we want to add.SyntaxSPACE(N)Here, N is an integer specifies the number of white spaces we want to add.Examplemysql> Select 'My Name is', Space(5), 'Ram'; +------------+----------+-----+ | My Name is | Space(5) | Ram | +------------+----------+-----+ | My Name is |          | Ram | +------------+----------+-----+ 1 row in set (0.00 sec)In the example above, SPACE() function adds 5 white spaces between the strings.

We can get the differences between the tables by unioning exclusion joins from 1st table to 2nd table and from 2nd table to 1st table. To understand it, we are taking the example of following two tables −mysql> Select * from value1; +-----+-----+ | i   | j   | +-----+-----+ |   1 |   1 | |   2 |   2 | +-----+-----+ 2 rows in set (0.00 sec) mysql> Select * from value2; +------+------+ | i    | j    | +------+------+ |    1 |   1  | |    3 |   3 ... Read More

Actually, INTERSECTION is just an inner join on all columns. We are taking a simple example of two tables, having the data as follows −mysql> Select * from value1; +------+------+ | i    | j    | +------+------+ | 1    | 1    | | 2    | 2    | +------+------+ 2 rows in set (0.00 sec) mysql> Select * from value2; +------+------+ | i    | j    | +------+------+ | 1    | 1    | | 3    | 3    | +------+------+ 2 rows in set (0.00 sec)Now, the following query will do the INTERSECTION between these tables −mysql> Select * from value1 join value2 using(i,j); +------+------+ | i    | j    | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.08 sec)

It can be understood with the help of an example in which two tables are having some values and we subtract the values with the help of LEFT JOIN. Here we are taking two tables having the following data −mysql> Select * from value_curdate; +----+----------+-------+ | Id | Product  | Price | +----+----------+-------+ | 1  | Notebook | 100   | | 2  | Pen      | 40    | | 3  | Pencil   | 65    | +----+----------+-------+ 3 rows in set (0.00 sec) mysql> Select * from value_prevdate; +----+-----------+-------+ | Id | Product   | ... Read More

Actually, sometimes we can avoid data-driven relationships in tables and we need to join them. It can be done with the help of CASE statement in the SELECT list to handle the joining possibilities. To understand it, we are taking the example of three data-driven tables namely ‘Student_Detail’ which have the following data −mysql> Select * from student_detail; +----+---------+ | Id | Name    | +----+---------+ | 1  | Harshit | | 2  | Rahul   | | 3  | Aarav   | +----+---------+ 3 rows in set (0.00 sec)Now, we have the three tables namely ‘Student_Harshit’, ‘Student_Rahul’, ‘Student_Aarav’ which ... Read More

In both the cases i.e. on not using ‘RIGHT’ or ‘LEFT’ keyword in the query, MySQL will return the result by taking it as INNER JOIN query. It is because the only difference between RIGHT, LEFT and INNER JOIN is the keyword of RIGHT or LEFT. 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; +----+--------+ | Id | Name   | +----+--------+ | 1  | Gaurav | | 2  | Rahul  | | 3  | Raman  | | 4  | Aarav  | +----+--------+ ... Read More

Basically, SOUNDEX() function is used to return the Soundex, a phonetic algorithm for indexing names after English pronunciation of sound, a string of a string. The proper structure for searching within MySQL using SOUNDEX() is as follows −SOUNDEX(Str)Here, Str is the string whose SOUNDEX string is to be retrieved.Examplemysql> Select SOUNDEX('MySQL'); +------------------+ | SOUNDEX('MySQL') | +------------------+ | M240             | +------------------+ 1 row in set (0.00 sec) mysql> Select SOUNDEX('harshit'); +--------------------+ | SOUNDEX('harshit') | +--------------------+ | H623               | +--------------------+ 1 row in set (0.00 sec) mysql> ... Read More

With the help of SOUNDS LIKE operator, MySQL search the similar sound values from the table. Syntax Expression1 SOUNDS LIKE Expression2 Here, both Expression1 and Expression2 will be compared based on their English pronunciation of sound. Example Following is an example from ‘student’ table which will match the two expressions based on the pronunciation of sound mysql> Select Id, Name, Address, Subject from student where name sounds like 'hrst'; +------+---------+---------+----------+ | Id | Name | Address | Subject | +------+---------+---------+----------+ | 15 | Harshit | Delhi | Commerce | +------+---------+---------+----------+ 1 row in set (0.00 sec)

The use of wildcards with RLIKE operators can save a lot of effort when we write a query that looks for some pattern (regular expression) in character string. The wildcards used with RLIKE are:^ − It signifies BEGINING of the string. In other words when we use this wildcard with RLIKE operator then it will find the pattern that begins with the particular string written after ^ wildcardExample mysql> Select Id, Name from Student WHERE Name RLIKE '^H';  +------+---------+  | id   | Name    |  +------+---------+  | 15   | Harshit |  +------+---------+  1 row in set (0.00 sec) $ − ... Read More