To remove \r from the text, you need to use REPLACE command. The syntax is as follows −UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’\r’, ’ ‘);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name text, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.79 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into removeDemo(Name) values('John\rSmithCarol'); Query OK, 1 row affected (0.13 sec) mysql> insert into removeDemo(Name) values('LarryMike\rSam'); ... Read More

To extract the Day/Month/Year from a timestamp, you need to use the date_parse() function. The syntax as follows −print_r(date_parse(“anyTimeStampValue”));The PHP code is as follows −$yourTimeStampValue="2019-02-04 12:56:50"; print_r(date_parse($yourTimeStampValue));The snapshot of PHP code is as follows −The following is the output −Array ( [year] => 2019 [month] => 2 [day] => 4 [hour] => 12 [minute] => 56 [second] => 50 [fraction] => 0 [warning_count] => 0 [warnings] => Array ( ) [error_count] => 0 [errors] => Array ( ) [is_localtime] => )The snapshot of the sample output −

You cannot get the type of variable in MySQL. Cast the type of variable into another using CAST operator. The syntax is as follows −SET @yourVariableName:=’anyValue’Use the CAST operator to cast to another type. The syntax is as follows −SELECT CAST( @yourVariableName AS SIGNED);To understand the above syntax, let us cast to another type.Case 1: String to unsigned −mysql> set @StringToInt:='12345'; Query OK, 0 rows affected (0.00 sec)The query is as follows to another type −mysql> select CAST(@StringToInt as UNSIGNED);The following is the output −+--------------------------------+ | CAST(@StringToInt as UNSIGNED) | +--------------------------------+ | 12345               ... Read More

To find invalid email address, use the below syntax −SELECT yourColumnName FROM yourTableName WHERE yourColumnName NOT LIKE '%_@_%._%';The above syntax will give the list of all invalid email addresses. To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FindInvalidEmailAddressDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> EmailAddress varchar(40),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.75 sec)Now you can insert some records in the table using insert command. We have inserted some invalid ... Read More

You can achieve this with the help of INSERT INTO SELECT statement. The syntax is as follows −INSERT INTO yourDatabaseName.yourTableName(SELECT *FROM yourDatabaseName.yourTableName);To understand the above syntax, let us create a table in a database and second table in another databaseThe database name is “bothinnodbandmyisam”. Let us create a table in the same database. The query is as follows −mysql> create table Student_Information    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(10),      -> Age int    -> ); Query OK, 0 rows affected (0.67 sec)Now you can insert some records in the ... Read More

You can use a dynamic query for this. First set the variable name for username and variable name for a password. The syntax is as follows −SET @anyVariableName=’yourUserName’; SET @anyVariableName1=’yourpassword’;Now you can use the CONCAT() function from MySQL. The syntax is as follows −SET @yourQueryName = CONCAT ('    CREATE USER "', @anyVariableName, '"@"localhost" IDENTIFIED BY "', @anyVariableName1, '" ' );Let us use the prepared statement PREPARE. The syntax is as follows −PREPARE yourStatementVariableName FROM @yourQueryName;Now you can execute the statement. The syntax is as follows −EXECUTE yourStatementVariableName;Deallocate the above using the DEALLOCATE PREPARE. The syntax is as follows −DEALLOCATE ... Read More

You need to use ADD KEY to make a column with Key=MUL. The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type, ADD KEY(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Instructor    -> (    -> Instructor_Id int,    -> Instructor_Name varchar(30),    -> Instructor_CourseName varchar(100)    -> ); Query OK, 0 rows affected (0.63 sec)Now you can look the table description of the table, the column KEY does not have any MUL key. The query is as follows to check the ... Read More

To get rid of this error message, let us see a sample example. But before that let us go through the concept to fix it.Use variable to get the value from stored procedure. The variable will prefix with @ symbol. The syntax is as follows −CALL yourStoredProcedureName(yourParameter1, yourParameter2, ..........N, @yourVariableName);To see the value of variable you need to use select statement. The syntax is as follows −SELECT @yourVariableName;To understand the above syntax, let us create a table and insert some records in the table.The query to create a table is as follows −mysql> create table StoredProcedureDemo    -> (   ... Read More

You don’t need to use two where clauses. Use two conditions using the LIKE operator and AND operator.To understand how to use LIKE for this, let us create a table. The query to create a table is as follows −mysql> create table WhereDemo    -> (    -> Id int,    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into WhereDemo values(101, 'Maxwell'); Query OK, 1 row affected (0.14 sec) mysql> insert into WhereDemo values(110, 'David'); Query ... Read More

In MySQL, you can use two different types of quotation marks which is backtick and another is single quotes or double quotes. In this case, maybe you are using single quotes to the column name that’s why you are getting error. You need to use the backtick symbol (` `) instead of single quotes. Backtick can be used with column names while single quotes can be used for strings.To understand the above error, let us create a table. The query to create a table is as follows −mysql> create table Backtick_SymbolDemo    -> (    -> Id int NOT NULL ... Read More