We can use CONCAT() function to combine the values of two or more columns. In this case, the arguments of the CONCAT() functions would be the name of the columns. For example, suppose we have a table named ‘Student’ and we want the name and address of the student collectively in one column then the following query can be written −mysql> Select Id, Name, Address, CONCAT(ID, ', ', Name, ', ', Address)AS 'ID, Name, Address' from Student; +------+---------+---------+--------------------+ | Id   | Name    | Address | ID, Name, Address  | +------+---------+---------+--------------------+ | 1    | Gaurav  | Delhi   ... Read More

MySQL allows us to pass only one argument to the CONCAT() function. In this case, MySQL returns the same argument as output. Following example will exhibit it −Examplemysql> Select Concat('Delhi'); +-----------------+ | Concat('Delhi') | +-----------------+ | Delhi           | +-----------------+ 1 row in set (0.00 sec)

A string function called CONCAT() is used to concatenate two or more strings as a single string in MySQL.SyntaxCONCAT(String1,String2,…,StringN)Here, the arguments of CONCAT functions are the strings which need to be concatenated as a single string.Examplemysql> Select CONCAT('Ram','is','a','good','boy') AS Remarks; +---------------+ | Remarks       | +---------------+ | Ramisagoodboy | +---------------+ 1 row in set (0.00 sec)

Whenever we want to pass an empty string as a parameter to BIT_LENGTH() function then we must have to pass blank quotes (even without any space). It cannot pass without quotes because MySQL then resembles it as the function without any argument and returns an error. But, when we pass an empty string with blank quotes then MySQL will return 0 as output. It can be understood with the following example as well −Examplemysql> Select BIT_LENGTH(); ERROR 1582 (42000): Incorrect parameter count in the call to native function 'BIT_LENGTH' mysql> Select BIT_LENGTH(''); +----------------+ | BIT_LENGTH('') | +----------------+ | 0 ... Read More

MySQL BIT_LENGTH() string function is used to get the length of the string in bits.SyntaxBIT_LENGTH(Str)Here Str, the argument of BIT_LENGTH() function, is the string whose BIT_LENGTH value is to be retrieved. Str can be a character string or number string. If it is a character string then it must be in quotes.Examplemysql> Select BIT_LENGTH('New Delhi'); +-------------------------+ | BIT_LENGTH('New Delhi') | +-------------------------+ | 72                      | +-------------------------+ 1 row in set (0.00 sec) mysql> select BIT_LENGTH(123456); +--------------------+ | BIT_LENGTH(123456) | +--------------------+ | 48                 | +--------------------+ 1 row in set (0.00 sec)

When BIN() string function is used with WHERE clause, the output returns by it will depend upon the condition given in WHERE clause. In this case, we must have to use binary value in WHERE clause. For example, suppose we have a table named ‘Student’ and we want to get only those rows where the binary value of column ‘id’ is higher than 1010, then we can write following query −mysql> Select *, Bin(id) from student where BIN(id) > 1010 ; +------+---------+---------+-----------+---------+ | Id   | Name    | Address | Subject   | Bin(id) | +------+---------+---------+-----------+---------+ | 15   ... Read More

Last_Insert_Id() MySQL function is used to find out which sequence number was assigned recently by AUTO_INCREMENT.Examplemysql> Create table Employee(Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(5)); Query OK, 0 rows affected (0.13 sec) mysql> Insert into Employee(Name) Values('Harvinder'); Query OK, 1 row affected (0.06 sec) mysql> Insert into Employee(Name) Values('Suresh'); Query OK, 1 row affected (0.07 sec) mysql> Select* from Employee; +----+---------+ | Id | Name    | +----+---------+ | 1  |Harvinder| | 2  | Suresh  | +----+---------+ 2 rows in set (0.00 sec) mysql> Select Last_insert_id(); +------------------+ | Last_insert_id() | +------------------+ |                2 | +------------------+ 1 row in set (0.00 sec)

With the help of the following command we can force MySQL out of TRADITIONAL mode −mysql> Set SQL_MODE =''; Query OK, 0 rows affected (0.00 sec)

In case of enabled SQL strict mode, subtraction between integers value in which one is of UNSIGNED type will produce an unsigned result by default. But MySQL produces an error if the result is a negative one. It can be observed with the following example −mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> Select CAST(0 AS UNSIGNED) -1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'The error after the query above shows that it is an overflow after numeric arithmetic expression.Now, it can be handled with ... Read More

MySQL exact-value arithmetic can handle overflow occurs during numerical expression assessment because overflow occurs depends on the range of the operands. The values used in arithmetic expressions changed to other data type can put away the overflow.For example, after converting the BIGINT Maximum value to DECIMAL while adding 1 to it can handle the overflow as follows −mysql> Select 9223372036854775807.0 + 1; +---------------------------+ | 9223372036854775807.0 + 1 | +---------------------------+ | 9223372036854775808.0     | +---------------------------+ 1 row in set (0.01 sec)