Found 4219 Articles for MySQLi

As we know that REPLACE () function is used to replace the occurrences of a substring with another substring within a string. We can also use REPLACE function with UPDATE statement to update the table by finding and replacing the data.Examplemysql> Update Student set Father_Name = REPLACE(Father_Name, 'Mr.', 'Shri '); Query OK, 5 rows affected (0.06 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> Select Name, Father_Name from Student; +---------+-----------------+ | Name    | Father_Name     | +---------+-----------------+ | Gaurav  | Shri Ramesh     | | Aarav   | Shri Sanjay     | | Harshit ... Read More

For using it with column’s data we need to provide column name as the argument of REPLACE() function. It can be demonstrated by using ‘Student’ table data as follows −Examplemysql> Select Id, Name, Subject, REPLACE(Subject, 's', ' Science') from Student WHERE Subject = 'Computers'; +------+--------+-----------+-----------------------------------+ | Id   | Name   | Subject   | REPLACE(Subject, 's', ' Science') | +------+--------+-----------+-----------------------------------+ | 1    | Gaurav | Computers | Computer Science                  | | 20   | Gaurav | Computers | Computer Science                  | +------+--------+-----------+-----------------------------------+ 2 rows in set (0.00 sec)

MySQL REPLACE() function can replace all the occurrences of a substring with another substring within a string.SyntaxREPLACE(str, find_string, replace_with)Here Str is a string which have the substring.Find_string is a substring which is present one or more times within the strung str.Replace_with is a substring which will replace every time it finds find_string within str.Examplemysql> Select REPLACE('Ram, My Name is Ram', 'Ram', 'Shyam'); +------------------------------------------------+ | REPLACE('Ram, My Name is Ram', 'Ram', 'Shyam') | +------------------------------------------------+ | Shyam, My Name is Shyam                        | +------------------------------------------------+ 1 row in set (0.00 sec)

We can use both INSTR() function and LIKE operator to search or match a particular pattern and they return same result. It can be demonstrated from the following example of ‘Student’ table.ExampleSuppose we want to search name, which contains ‘av’ in it, from ‘Student’ table. We can use INSTR() function as follows −mysql> Select Name from student where INSTR(name, 'av') > 0; +--------+ | Name   | +--------+ | Gaurav | | Aarav  | | Gaurav | +--------+ 3 rows in set (0.00 sec)Now, for the same kind of search we can use LIKE operator as follows −mysql> Select Name ... Read More

Actually, in MySQL, we can prepare a statement with the same name without de-allocating the earlier one because MySQL automatically drops the prepared statements when they are redefined or when we close the connection to the server. In other words, we can say that we can use the same name for prepared statements without de-allocating them explicitly. But, to free the memory on the server side we must have to de-allocate them. It can be done with the help of DEALLOCATE statement as follows −DEALLOCATE PREPARE statement;Here statement is the name of the prepared statements.DROP PREPARE statements is the synonym ... Read More

As we know that MySQL user variables are specific to client connection within which they are used and exist only for the duration of that connection. When a connection ends, all its user variables are lost. Similarly, the prepared statements also exist only for the duration of the session in which it is created and it is visible to the session in which it is created. When a session ends, all the prepared statements for that session are discarded.Another similarity is that prepared statements are also not case-sensitive like MySQL user variables. For example, stmt11 and STMT11 both are same ... Read More

It can be understood with the help of following the example in which we have updated the table named ‘Student’, having the following data, by using prepared statement −mysql> Select * from Student; +------+-------+ | Id   | Name  | +------+-------+ | 1    | Ram   | | 2    | Shyam | | 3    | Mohan | +------+-------+ 3 rows in set (0.00 sec) mysql> PREPARE stmt11 FROM 'UPDATE Student SET Name = ? WHERE Id = ?'; Query OK, 0 rows affected (0.03 sec) Statement prepared mysql> SET @A = 'Sohan', @B = 3; ... Read More

It can be understood with the help of following the example in which we have created the table named ‘Student’ by using prepared statement −mysql> PREPARE stmt3 FROM 'CREATE TABLE Student(Id INT, Name Varchar(20))'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> EXECUTE stmt3; Query OK, 0 rows affected (0.73 sec) mysql> DEALLOCATE PREPARE stmt3; Query OK, 0 rows affected (0.00 sec)Now, with the help of following queries using prepared statements, we can insert the valuesin table ‘Student’ −mysql> PREPARE stmt7 FROM 'INSERT INTO Student(Id, Name) values(?, ?)'; Query OK, 0 rows affected (0.00 sec) Statement ... Read More

Actually, it is not possible to prepare all SQL statements because MySQL only allows the following kinds of SQL statements that can be prepared:SELECT statementsExamplemysql> PREPARE stmt FROM 'SELECT tender_value from Tender WHERE Companyname = ?'; Query OK, 0 rows affected (0.09 sec) Statement prepared mysql> SET @A = 'Singla Group.'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE stmt using @A; +--------------+ | tender_value | +--------------+ |   220.255997 | +--------------+ 1 row in set (0.07 sec) mysql> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.00 sec)INSERT, REPLACE, UPDATE and DELETEstatements that modify the ... 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.