- 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 6702 Articles for Database
136 Views
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)
258 Views
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)
460 Views
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
60 Views
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
64 Views
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
108 Views
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
116 Views
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
75 Views
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
80 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.
59 Views
Suppose if we want to make the output of REPEAT() function more readable then we can use another function/s with it. For example, if we want to add space or some other character between the repeated values then we can use CONCAT() function.Examplemysql> Select REPEAT(CONCAT(' *', Subject, '* '), 3)AS Subject_repetition from student; +-----------------------------------------+ | Subject_repetition | +-----------------------------------------+ | *Computers* *Computers* *Computers* | | *History* *History* *History* | | *Commerce* *Commerce* *Commerce* | | *Computers* *Computers* *Computers* | ... Read More