Featured
-
No Featured Found!
Tags
REPEAT(str,count) mysql function
REPEAT(str,count)
If you want to repeat a string str at given times then you can use the REPEAT mysql function. REPEAT function returns a string consisting of the string str repeated count times. It returns an empty string if count is less tha...
QUOTE(str) mysql string function
QUOTE(str)
Quotes function used to produce a result that can be used as a properly escaped data value in an SQL statement of a string. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single q...
MAKE_SET(bits,str1,str2,...) mysql function
MAKE_SET(bits,str1,str2,...)
MAKE_SET function returns a set of values which are from string containing substrings separated by , characters, consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str...
LTRIM(str) Function mysql
LTRIM(str)
LTRIM(str) function returns the string str after remove the leading space characters means if you want to remove the all spaces from the left side of a string in mysql you can use this LTRIM mysql function.
Here I am giving a examp...
LPAD(str,len,padstr) function mysql
LPAD(str,len,padstr)
LPAD mysql funtion returns the string str, left-padded with the string padstr to a length of len characters means if you want to extend a string to a certain length and insert some character in left side of string at the p...
LOWER(str) mysql function
LOWER(str)
LOWER(str) function returns the string str with all characters changed to lowercase according to the current character set mapping. The default character set is latin1 (cp1252 West European).
Here is a example of this function-
...
LOCATE(substr,str)/LOCATE(substr,str,pos) function mysql
LOCATE(substr,str), LOCATE(substr,str,pos)
LOCATE function returns the position of first occurrence substring in passed string. The second syntax returns the first occurrence of substring after the given position (the thirds parameter of secon...
LENGTH(str) MySQL function
LENGTH(str)
LENGTH(str) mysql function returns the length of the string str, measured in bytes. This function counts a multibyte character as multiple bytes. hence it means for a string containing 4(four) 2-byte characters then LENGTH() return...
LEFT(str,len)
LEFT(str,len)
The LEFT mysql function returns the leftmost len characters from the string str or returns NULL if any argument is NULL.
The LEFT function is multibyte safe.
Here are some example which will explain you this function-
...
INSERT(str,pos,len,newstr) MySQL function
INSERT(str,pos,len,newstr)
If you want to insert a string inside another string then you can use this MySQL INSERT function. INSERT returns the string str, with the substring beginning at position pos and len characters long replaced by the st...
HEX mysql function
HEX(str), HEX(N)
A hexadecimal string is returened by HEX(), it is a representation of str for a string argument str, where each byte of each character in str is converted to two hexadecimal digits ans multibyte characters are converted more ...
FROM_BASE64('encocded_string) function mysql
FROM_BASE64('string')
FROM_BASE64 function in mysql is used to decode the encoded value. The value which is encode with the BASE64 rule to encode by the MySQL function TO_BASE64, is decoded by this FROM_BASE64 mysql function.
The argument whi...
TO_BASE64(str) function MySQL
TO_BASE64('string')
TO_BASE64 function in mysql is used for encode any string with the string encode rule BASE64. This function returns a encoded string.
This function is added with the mysql version 5.6.1
In mysql there is different base-...
How to add a column into a table in MySQL?
Sometimes we need to add a column that we require into the table. We can do this by using ADD keyword with ALTER command.
Syntax:
To add a column into a table, use the following syntax:
ALTER TABLE table_name
ADD COLUMN column_name data...
How to drop a column from table in MySQL?
Sometimes we need to drop a column that we don't need further from the table. We can do this by using DROP keyword with ALTER command.
Syntax:
To drop/delete the column from a table, use the following syntax:
ALTER TABLE table_name
DROP...
How to change the datatype of a column in MySQL?
Sometimes we need to change the data type of a column in a table. We can do this by using Modify keyword with ALTER command.
Syntax:
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
MODIFY...
Difference between UNION and UNION ALL operators in MySQL.
In MySQL UNION operator used to combine the result of multiple SELECT statements. When we apply the UNION operator then it selects only distinct values by default (No duplicate values are allowed).
UNION Operator
When we apply UNION operato...
How to insert data from one table to another in MySQL?
We can insert data from one table to another in MySQL. For this we use the INSERT INTO SELECT statement that selects the data from on table and inserts that data into another table.
INSERT INTO SELECT Syntax
We can select and insert all the...
FORMAT(X,D[,locale]) function mysql
FORMAT(X,D[,locale])
In mysql FORMAT function is used for formats a number X to a format like '#,###,###.##' and rounded to decimal places, and it returns the result as a string. If D is 0, the result has no decimal point.
The third optional ...
MyISAM vs INNODB in mysql
Difference between MYISAM and INNODB are given below.
MYISAM:
1 MyISAM does not support foreign keys.
2 MyISAM stores its indexes,data and tables in disk space using separate three different files
3 MYISAM not supports transaction
...
FIND_IN_SET(str,strlist) function in mysql
FIND_IN_SET(str,strlist)
FIND_IN_SET function returns a value between 1 to N if the string str is in the string list strlist consisting of N substrings. If the first argument is a constant string and the second is a column of type SET, the F...
How to Concatenate two or more string in MySQL
This article explains the MySQL string functions which are used to concatenate two or more strings.
1. CONCAT : CONCAT() function is used to generate a string by concatenating two or more strings. The function take one or more strings as argu...
FIELD string function mysql
FIELD(str,str1,str2,str3,...)
FIELD function is used to get the index (position) of str in the str1, str2, str3, ... list and it returns 0 if str is not found. In the field list if all arguments are strings then all arguments are compared as...
ELT function mysql
ELT(N,str1,str2,str3,...)
The MySQL ELT function is used for get the Nth element of all listed arguments in the function. The first argument is used for which number of element you want to get. In the argument list the if N = 1 then return str...
CONCAT_WS function in MySQL
CONCAT_WS(separator,str1,str2,...)
The full form of CONCAT_WS is concatenate with Separator which is a special form of CONCAT(). The first argument in CONCAT_WS is used as a separator for concatenate all other arguments. The separator is adde...
CONCAT string function mysql
CONCAT(str1,str2,...)
CONCAT() string function in mysql returns the string that results from concatenating the all input arguments.
CONCAT may have one or more arguments. If all arguments are nonbinary strings then their result will be a non...
Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL
In MySQL join is used to combine records from 2 or more tables based on a common column between them.
INNER JOIN: It returns all rows when there is a match in both tables.
LEFT JOIN: It returns all rows from the left table and matched rows ...
CHAR_LENGTH(str) & CHARACTER_LENGTH(str) MySQL string function
CHAR_LENGTH(str)
The string str length measured in characters is returned by CHAR_LENGTH. It also counts a single character for a multibyte character. This means, for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHA...
How to get count of duplicate values from table in mysql?
Sometimes we need to check how many duplicate values a column has in MySQL table. We can do this by using "group by" and "having".
Example: Suppose you have a table "user" with "fist_name" column from which you want to find all the records w...
How to count unique values from table in mysql?
Sometimes we have the requirement to count how many unique values are in the table. We can do this by using "Distinct" and count().
SELECT COUNT(DISTINCT column_name) FROM table_name;
Example: Suppose you have a table user from which you ...
CHAR(N,....) string function MySQL
If the length of results is greater then the value if the max_allowed_packet system variable then the String-valued functions will return NULL
The first position of a string (for those functions which operate on string), is numbered 1.
Non-...
BIT_LENGTH function mysql
BIT_LENGTH(str)
BIT_LENGTH function returns the length of the string str in bits.
for example,
the bit length of text is 32 and hello is 40.
mysql> SELECT BIT_LENGTH('text');
+--------------------+
| BIT_LENGTH('text') |
+--------...
BIN (N) string function mysql
String-valued functions return NULL, if the the result's length would be greater than the value of the max_allowed_packet system variable.
The first position of a string (for those functions which operate on string), is numbered 1.
Noninteg...
Use of LIKE in MySQL
This article explains how to use LIKE operator to get data from MySQL table based on pattern matching. The LIKE operator used with WHERE clause of SELECT, UPDATE and DELETE statement which search for a specified pattern in a column.
In MySQL L...
ASCII(str) String Function MySQL
String-valued functions return NULL, if the the result's length would be greater than the value of the max_allowed_packet system variable.
The first position of a string (for those functions which operate on string), is numbered 1.
Noninteg...
Separate a column value into 2 column in MySQL table
Hello friends,
I am here to share a issue which I faced once, That is I want to separate a columns values into 2 columns.
So if you are also looking to separate a columns value into 2 columns, You can use following syntax-
UPDATE your_tab...
Change Column data-type from VARCHAR to DATE
Hello friends,
I am here for share the solution of a MySQL problem.
If you want to change the data type of a column from varchar or any other one to date, you should need to use following query-
UPDATE `tbl` SET `date1` = STR_TO_DATE(`date...
Temporary Table
Temporary table is used to store the temporary record which can be reused in a single session. Temporary tables are created from the existing tables and these tables are drops once the session ends or connection is terminated but you can also use...
Get n number of highest and lowest records in MySQL
In my previous blog How to use Limit clause in MySQL Query, there is explanation of LIMIT clause and its use with SELECT statement. This article explains the use of LIMIT with ORDER BY clause to get 'n' number of Highest and Lowest records in MyS...
How to use Limit clause in MySQL Query
This tutorial explains how to use limit clause within MySQL query to fetch records. The MySQL LIMIT clause is used to limit the number of records returned from MySQL query by using value passed with LIMIT clause. Limit clause is used with the SEL...
How to check indexes for a database or table in mysql ?
There are chances that you want to check for the indexes that you created on the tables in past because there can be a large number of indexes on numerous of tables depending on the size of the database
If you want to see the indexes for a par...
Handling Duplicates using INSERT IGNORE
INSERT IGNORE:
INSERT IGNORE is used for handling duplicacy in a table as it ignores the query if the data is already present in the table and if the data does not exist then new row will be inserted.
Syntax:
INSERT IGNORE INTO tablename...
MySQL "IF" function
The function that returns a value based on a condition is MYSQL IF function. Hence MYSQL IF function is one of the control flow functions.
The syntax of the MySQL IF function is as follows:
IF(expr,if_true_expr,if_false_expr)
For examp...
"IF ELSE" statement in MySQL
The statement which allows you to execute a set of SQL ststements on the basis of certain conditions is known as MYSQL IF statement.
Below I am explaining the structure for if-else statement.
MySQL IF statement syntax-
IF expression THE...
What is the join in MySQL ?
Welcome to findnerd, today we are going to discuss to join in MySQL.
Firstly let know what is join ?
An SQL JOIN clause is used to combining two or more tables and return output in single table.
So in simple word we can say that MySQL ...
Pattern Matching using REGEXP
REGEXP:
It is similar to LIKE which is used to fetch data based on regular expression from a table.
Syntax:
SELECT columnname(s) FROM tablename WHERE columnname REGEXP pattern;
Example 1:
SELECT name FROM Employee WHERE name R...
Mysql order by values within IN() function
Hello guys,
Most of time we face the problem order by within IN() values . Mysql provides the function FIELD(), It will sort fetched IN() data according to requirment . The FIELD function returns the position of the first string in the remaining...
What is the mysql Constraints?
Welcome to FindNerd,
Today we are going to discuss about MySQL Constraints.
Firstly let know what is Constraints in MySQL ?
Constraints:- Constraints are the utilities that restrict ingression of data into the table if the designated co...
Stored routies in Mysql
Welcome to Findnerd. We are going to explain the storage procedure and functions. It is also known as stored routines. Stored routines is available from Mysql5.7.
There is table named proc which is required to work with stored routines. It will ...
Use of Transaction in DBMS
A transaction is any unit of work done against the database.A transaction can be used to save the changes made to the database, it can be used to rollback the database to any save point.
For example say if you are creating or updating a record...
