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
MySQLi Articles
Page 326 of 341
How to remove special characters from a database field in MySQL?
You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.The syntax is as follows to remove special characters from a database field.UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’yourSpecialCharacters’, ’’);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table RemoveSpecialCharacterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY Key(Id) -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using ...
Read MoreMySQL concat() to create column names to be used in a query?
To create column names to be used in a query, you need to use a user-defined variable with the set command. The syntax is as follows −SET @anyVariableName := ( SELECT CONCAT ( "SELECT", GROUP_CONCAT(CONCAT(" 1 as ", COLUMN_NAME) SEPARATOR ', '), " FROM DUAL") FROM INFORMATION_SCHEMA_COLUMNS WHERE TABLE_NAME= ‘yourTableName’ );Now prepare the statement using the PREPARE command. The syntax is as follows −PREPARE anyVariableName from @anyVariableName;Execute statement using EXECUTE command. The syntax is as follows −EXECUTE anyVariableName;Deallocate the prepared statement using DEALLOCATE command. The syntax is as follows −DEALLOCATE PREPARE anyVariableName; ...
Read MoreImprove MySQL Search Performance with wildcards (%%)?
No, MySQL won’t improve search performance whenever you have leading wildcards because MySQL will be unable to use the index. If you change to ‘anyLetter%’ then it will be able to use indexThe below syntax is better to use with trailing wildcards. The syntax is as follows −SELECT *FROM yourTableName WHERE yoorColumnName LIKE ‘anyLetter%’;The query to create a table is as follows −mysql> create table TrailingWildCardDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name Varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 ...
Read MoreFind rows where column value ends with a specific substring in MySQL?
To find rows and update with new value where column value ends with specific substring you need to use LIKE operator.The syntax is as follows:UPDATE yourTableName SET yourColumnName=’yourValue’ WHERE yourColumnName LIKE ‘%.yourString’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table RowEndsWithSpecificString -> ( -> Id int NOT NULL AUTO_INCREMENT, -> FileName varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.50 sec)Now you can insert some records in the table using ...
Read MoreSearch for text between delimiters in MySQL?
You need to use LOCATE() along with SUBSTR(). The below syntax will find the word after delimiter. Here, delimiter is colon(:), you can use another i.e. it is up to you. The syntax is as follows −SELECT SUBSTR(yourColumnName, LOCATE(':', yourColumnName)+1, (CHAR_LENGTH(yourColumnName) - LOCATE(':', REVERSE(yourColumnName)) - LOCATE(':', yourColumnName))) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SearchTextBetweenDelimitersDemo -> ( -> ...
Read MoreSorting a VARCHAR column as FLOAT using the CAST operator isn’t working in MySQL ?
If your cast does not work, then you can use yourColumnName*1 with ORDER BY clause.Using yourColumnName*1. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY yourColumnName*1 DESC;You can also use CAST() operator. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY CAST(yourColumnName as DECIMAL(8, 2)) DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharColumnAsFloatDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Amount varchar(20), -> PRIMARY KEY(Id) ...
Read MoreEmpty string in not-null column in MySQL?
In PHP, the empty string equals to a NULL value, but in MySQL, the case is the different i.e. empty string is not equal to NULL value. To understand the above syntax, let us create a column with NOT NULL constraint while you can insert an empty string.Let us create a table. The query to create a table is as follows −mysql> create table EmptyStringNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10) not null, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records ...
Read MoreResolve an error whenever multiple rows are returned in MySQL Benchmark?
You will get an error whenever you return multiple rows in the benchmark. Return a scalar value or single row instead of multiple rows. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE yourCondition.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserDemo -> ( -> UserId int, -> UserName varchar(20), -> RegisteredCourse varchar(10) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserDemo values(1, ...
Read MoreMySQL- GROUP and COUNT by date?
You can use GROUP BY clause and COUNT() function for this. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, ..N, COUNT(*) as anyAliasName FROM yourTableName GROUP BY yourColumnName1, yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table GroupAndCountByDate -> ( -> Id int NOT NULL AUTO_INCREMENT, -> TripDate date, -> ShopId int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.79 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ...
Read MoreReturn order of MySQL SHOW COLUMNS?
To return order of MySQL SHOW COLUMNS, you need to use ORDER BY clause. The syntax is as follows −SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘yourTableName’ AND column_name LIKE 'yourStartColumnName%' ORDER BY column_name DESC;Let us create a table in database TEST. The query to create a table is as follows −mysql> create table OrderByColumnName -> ( -> StudentId int, -> StudentFirstName varchar(10), -> StudentLastName varchar(10), -> StudentAddress varchar(20), -> StudentAge int, -> StudentMarks int ...
Read More