When we use CONCAT_WS() function with WHERE clause then the output would be based upon the condition provided in WHERE clause. It can be understood from the example of ‘Student’ table as followsExamplemysql> Select CONCAT_WS(' ',Name, Last_name, 'Resident of', Address, 'is studying', Subject)AS 'Student Detail' from student WHERE id = 20; +----------------------------------------------------------------+ | Student Detail                                                 | +----------------------------------------------------------------+ | Gaurav Rathore Resident of Jaipur is studying Computers        | +----------------------------------------------------------------+ 1 row in set (0.00 sec)

NULL as both argumentsMySQL returns blank output if we will use NULL as both of the arguments in CONCAT_WS() function.Examplemysql> Select CONCAT_WS('', NULL, NULL); +-------------------------+ | CONCAT_WS('', NULL, NULL) | +-------------------------+ |                         | +-------------------------+ 1 row in set (0.00 sec)NULL as one of the argumentMySQL returns the value of the other argument as output if we will use NULL as one of the argument in CONCAT_WS() function.Examplemysql> Select CONCAT_WS('', NULL, 'Delhi'); +----------------------------+ | CONCAT_WS('', NULL, 'Delhi') | +----------------------------+ | Delhi                 ... Read More

Both CONCAT() and CONCAT_WS() functions are used to concatenate two or more strings but the basic difference between them is that CONCAT_WS() function can do the concatenation along with a separator between strings, whereas in CONCAT() function there is no concept of the separator. Other significance difference between them is that CONCAT()function returns NULL if any of the argument is NULL, whereas CONCAT_WS() function returns NULL if the separator is NULL.ExampleThe example below demonstrate the difference between CONCAT() and CONCAT_WS() function −mysql> Select CONCAT('Ram', 'is', 'a', 'good', 'student') AS 'Example of CONCAT()'; +---------------------+ | Example of CONCAT() | +---------------------+ ... Read More

As we know TEXT data objects are useful for storing long-form text strings. The different TEXT objects offer a range of storage space from 255 bytes to 4 Gb. The following table shows the storage of different kinds of TEXT data type −Type of BLOBMaximum amount of Data that can be storedOverheadTINYTEXTUp to 255 bytes1 byteTEXTUp to 64 Kb2 bytes MEDIUMTEXTUp to 16 Mb3 bytesLONGTEXTUp to 4 Gb4 bytes

TEXT data objects are useful for storing long-form text strings in a MySQL database. Followings are some point about TEXT data type −TEXT is the family of column type intended as high-capacity character storage.The actual TEXT column type is of four types-TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT.The four TEXT types are very similar to each other; the only difference is the maximum amount of data each can store.The smallest TEXT type, TINYTEXT shares the same character length as VARCHAR.TEXT values are treated as character strings.TEXT has character set other than binary character set and collation.The comparisons and sorting are based on ... Read More

We can use the output of CONCAT() function which is applied to the column/s of a MySQL with the column/s of another MySQL table. It can be done with the help of MySQL join.ExampleFor example, we have two table ‘Student’, having the details like id, Name, Last_name, Address and Subjects of the students, and ‘Remarks’, having the id and comments about the students. Now, the following query can combine CONCAT() function with another table column −mysql> Select * from remarks; +------+-------------+ | ID   | Comment     | +------+-------------+ | 1    | Good        | | ... Read More

As we know that BLOB is a binary large object that can hold a variable amount of data. The different TEXT objects offer a range of storage space from 255 bytes to 4 Gb. Following table shows the storage of different kinds of BLOB data type −Type of BLOBMaximum amount of Data that can be storedOverhead TINYBLOBUp to 255 bytes1 byteBLOBUp to 64 Kb2 bytes MEDIUMBLOBUp to 16 Mb3 bytes LONGBLOBUp to 4 Gb1 Bytes

As we know that wildcards are characters that help search data matching complex criteria. Wildcards are used in conjunction with LIKE comparison operator or NOT LIKE comparison operator. MySQL allows us to match the data, from the output of CONCAT() function, with the help of wildcard and comparison operators LIKE or NOT LIKE. An example from ‘Student’ table is given to make it clearer.Examplemysql> Select CONCAT(Name, ' ', Last_name) AS NAME from student Where CONCAT(Name, ' ', Last_Name) LIKE '%Kumar%'; +---------------+ | NAME          | +---------------+ | Gaurav Kumar  | | Harshit Kumar | +---------------+ ... Read More

As we know that CONCAT() function will return NULL if any of the argument of it is NULL. It means MySQL will return NULL if we pass column name, containing a NULL value, as one of the arguments of CONCAT() function. Following is an example of ‘Student’ table to explain it.ExampleIn this example, we are concatenating the values of two strings and at 5th row one, the value is NULL hence the concatenation result is also NULL.mysql> Select Name, Address, CONCAT(Name, ' Resident of ', Address)AS 'Detail of Student' from Student; +---------+---------+---------------------------+ | Name    | Address | Detail ... Read More

Suppose from the table ‘Student’ we want to concatenate the values of columns, ‘Name’, ‘Address’ and ‘Columns’, based on the condition that is also a concatenation of values from columns, ’Name’, ‘Subject’, provided in WHERE clause with the help of CONCAT() function. We can use the following query to give the output −mysql> Select CONCAT(Name, ' ', 'Resident of', ' ', Address, ' ', 'is', ' ', 'Studying', ' ', Subject)AS 'Detail of Student' from Student WHERE CONCAT(Name, Subject) = "AaravHistory"; +----------------------------------------------+ | Detail of Student                            | ... Read More