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
Database Articles
Page 528 of 546
Can MySQL automatically convert empty strings to NULL?
You need to use NULLIF() function from MySQL. The syntax is as follows:SELECT NULLIF(yourCoumnName, ’ ’) as anyVariableName from yourTableName;In the above syntax, if you compare empty string( ‘ ‘) to empty string( ‘ ‘), the result will always be NULL. However, if you compare with NULL to empty string( ‘ ‘) then also the result will always be NULL.To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConvertEmptyStringToNULL -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY KEY(Id) ...
Read MoreHow to match one character in MySQL in place of %?
To match only one character in MySQL, you can use underscore(_) in place of %. The syntax is as follows:SELECT *FROM yourTableName 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 OneCharactermatchDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PassoutYear year, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into OneCharactermatchDemo(PassoutYear) values('2008'); Query OK, 1 row affected (0.14 sec) mysql> ...
Read MoreChanging Column in MySQL from int to double?
To change the column in MySQL from int to double you need to use ALTER TABLE command.The syntax is as follows:ALTER TABLE yourTableName modify column yourColumnName DOUBLE NOT NULL;If you want NULL value then remove NOT NULL from the above syntax. The syntax is as follows:ALTER TABLE yourTableName modify column yourColumnName DOUBLE;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table IntToDoubleDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> CylinderVolume int, -> PRIMARY KEY(Id) -> ); Query OK, ...
Read MoreHow can I add a new column which counts the number of rows as serial number in MySQL?
To add a new column that counts the number of rows as serial number, you can use the global variable in select statement.Let us create a table. The query to create a table is as follows:mysql> create table addColumnToCountAsSerialNumber -> ( -> Id int, -> Name varchar(20), -> Age int, -> Salary int -> ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into addColumnToCountAsSerialNumber values(10, 'John', 23, 8576); Query OK, 1 row affected (0.10 sec) mysql> insert into addColumnToCountAsSerialNumber values(12, ...
Read MoreDoes MySQL update with regexp possible?
You cannot update with regexp i.e. you need to use LIKE operator instead of regexp. MySQL does not provide support for update with regexp. The LIKE operator is as follows:UPDATE yourTableName SET yourColumnName= REPLACE(yourColumnName, yourValue)', '' ) WHERE yourColumnNameLIKE '%yourValueThatWillReplace)%';To understand the above syntax, let us create a table.mysql> create table Replace_Demo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Value varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into Replace_Demo(Value) values('221)'); Query OK, 1 ...
Read MoreMySQL truncate text with ellipsis?
You can truncate the text with ellipsis using LENGTH() with CASE statement. If your length is greater than 7 then truncate the text and add some number otherwise print the number as it is.To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table TruncateText -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Number longtext, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into TruncateText(Number) values('64575868667687'); ...
Read MoreConditional NOT NULL case MySQL?
For conditional NOT NULL case, you do not need to use and = operator. You need to use IS NULL and IS NOT NULL property because NULL is a special case in MySQL.To understand the conditional NOT NULL case, let us create a table. The query to create a table is as follows:mysql> create table ConditionalNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> SendMessage longtext, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table ...
Read MoreSplit a string and loop through values in MySQL Procedure?
To split a string and loop through all values in MySQL procedure, you do not need to use REPLACE() function. To understand, first create a stored procedure and after that create a table and call the stored procedure with some values. The value will be inserted into the table.The query to create a stored procedure is as follows:mysql> DELIMITER // mysql> CREATE PROCEDURE SP_SplitString(Value longtext) -> BEGIN -> DECLARE front TEXT DEFAULT NULL; -> DECLARE frontlen INT DEFAULT NULL; -> DECLARE TempValue TEXT DEFAULT NULL; -> iterator: -> LOOP -> IF LENGTH(TRIM(Value)) ...
Read MoreMySQL stored procedure return value?
To return a value from stored procedure, you need to use user defined session specific variable. Add @ symbol before variable name.For example, use @symbol for variable valido. The syntax for the same is as follows:SELECT @valido;Whenever you use select statement, you need to use @anyVariableName. The syntax is as follows:SELECT @anyVariableName;Here is the demo of stored procedure of return value. The query to create a stored procedure is as follows:mysql> create procedure ReturnValueFrom_StoredProcedure -> ( -> In num1 int, -> In num2 int, -> out valido int ...
Read MoreRetrieve a large select by chunks in MySQL?
To retrieve large select by chunks, you need to use ORDER BY LIMIT. The syntax is as follows:SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 0, 10;From the above syntax, you will get 10 rows from the table. In the above syntax, 0 represents the first row from the result set of a table that means it is zero index based. The second value of LIMIT represents the maximum number of rows that can be retrieved from the table.If you want the next rows after 10 to 30, then use in LIMIT like this. The syntax is as follows:SELECT *FROM yourTableName ...
Read More