MySQL String Function

Ads

In this section you can learn about the uses of MySQL String functions. These functions can be used to manipulate the string data. Here we have compiled the almost all String functions that you can use to learn more about string functions supported by MyS

MySQL String Function

In this section you can learn about the uses of MySQL
String functions.These functions can be used to manipulate the string data. Here we have compiled
the almost all String functions that you can use to learn more
about string functions supported by MySQL.

ASCII (str)The ASCII(str) function returns the ASCII code value of the leftmost character of the
String str. Returns 0 if str is the empty string. Returns NULL if
str is NULL.
Here are the some example of the ASCII(str) function:

BIN (N)The BIN string function return a string value representing
of the binary value of N, where N is a longlong(BIGINT) number. This function
is equivalent to CONV(N, 10 , 0). If the function return the null then N is
null. Here are the some example of the BIN(N) function:

mysql> SELECT BIN(5);
-> '101'

BIT_LENGTH (str)The BIT_LENGTH(str) function return the String str length in bits .
Here are the some example of the BIT_LENGTH(str) function:

mysql> SELECT BIT_LENGTH('a');
-> 8

CHAR(N,... [USING charset_name] )The CHAR(N,... [USING charset_name] ) function return a string
consisting the character and given the integer value. This function skipped
the NULL values. Here are the some
example of the CHAR(N,... [USING charset_name] ) function: Example:

CHAR_LENGTH(str)The CHAR_LENGTH(str)
function returns String str lengths that is measured in characters. But in
this function a multi-byte character counts as single character such as a
string contains 5 two-byte characters, then LENGTH() function returns 10,
but the CHAR_LENGTH() returns 5.

CHARACTER_LENGTH(str)
This function is same as CHAR_LENGTH().

CONCAT(str1, str2..)The CONCAT(str1, str2?.)
function can have one or more arguments and its returns a string that is the
result of concatenating the arguments. In this function all arguments are
non-binary strings then the result is also non-binary string but if any
argument is binary string then result is binary string. And a numeric
argument is converted to its equivalent binary string form. But if any
argument is NULL then it also returns NULL.Example:

CONCAT_WS(separator str1, str2,....) The CONCAT_WS() means CONCAT
With Separator. The first argument is treated as a separator for the rest of
the arguments and it is added between the strings for concatenating. If the
separator is NULL then the result is NULL.Example:

CONV (N, from_base, to_base)The CONV (N, from_base,
to_base) function is used to convert the number between different number
bases. This function returns a String that is representation of number N. It
convert the number N from base from_base to base to_base. But it returns
NULL if any argument is NULL. In this function argument N is interpreted as
an integer, but it can be specified as a string or as a integer also. The
minimum and the maximum base is 2 and 36 respectively. But if to_base is
negative then N is treated as a signed number else it is treated as a
unsigned number. Example:

ELT(N,str1,str2,str3,...)The ELT(N, str1, str2,
str3,..) function returns str1 if N=1 and str2 if N=2 and so on. But it
returns NULL if N is greater than the total number of arguments or less than
1. Here are some
example of the ELT(N, str1, str2, str3,..) function: Example:

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])The EXPORT_SET(bits, on,
off[, separator[,number_of_bits]]) function returns a string for a every bit
set in the value bits, then you get the on String but for every reset bit
you can get the off string. In this function Bits in bits are examined from
right to left but Strings are concatenate to the result from left to right.
The number of bits are calculated by given number_of_bits that?s default
value is 64.Example:

FIELD(str,str1,str2,str3,...)The FIELD(str,str1,str2,str3,....) function is used to find the index position of
str in the arguments str1,str2,str3. In other words it returns the index position of
str in the arguments. It returns 0 if str is not available in the arguments. If
str is NULL then return value is 0 because NULL fails equality
comparison with any value.
Example:

FIND_IN_SET(str,strlist)
The FIND_IN_SET(str, strlist) function returns a value in the range of 1 to N. This function find the
String str in the substring of String list strlist and return the index value. This String list have many substrings that is separated by ?,? characters. This function returns 0 when
str is not available in stringlist or string list is the empty string.
Example:

mysql> SELECT FIND_IN_SET('2',
'1,2,3,4');
-> 2

FORMAT(X,D)The FORMAT(X,D) function formats the number X like #,###,###.## and rounded the decimal places to
D then returns the string as a result. But if D is 0 then the result don?t have fractional part.
Some examples of the FORMAT(X, D) function are given below :Example:

HEX(N_or_S)In HEX(N_or_S) function N_or_S is a number then this function returns a string that is representation of hexadecimal value of N, where is a longlon(BIGINT) number. But if
N_or_S is a string, then it returns a string hexadecimal representation of
N_or_S where each character in N_or_S is converted to two hexadecimal digits. Here are some example of the HEX(N_or_S)
function are given below:Example:

mysql> SELECT HEX(10);
-> 'A'
mysql> SELECT HEX( 'abd');
-> 616264

INSERT(str,pos,len,newstr)The INSERT(str, pos, len, newstr) function is used to replace some part or whole String of String
str with String newstr from beginning at position
pos and len character long. This function returns the String
str if pos is not within the length of the string. It returns NULL if any argument is NULL.Example:

LOWER(str)
The LOWER(str) function return the String str. And in this
String all the
characters are changed in the lowercase.

mysql> SELECT LOWER('ROSEINDIA');
->'roseindia'

LCASE(str)The LCASE(str) function issame as LOWER() function

LEFT(str,len)The LEFT(str, len) function returns the leftmost len characters from the
String str.
Here are the some example of the LEFT(str, len) function:Example:

mysql> SELECT LEFT('roseindia', 4);
-> 'rose'

LENGTH(str)The LENGTH(str) function returns the length of the String str in bytes. Here are the
some example of the LENGTH(str) function:Example:

mysql> SELECT LENGTH("roseindia");
->'9'

LOAD_FILE(file_name) The LOAD_FILE(file_name) function is used to read the file and this function returns the content of file as a string. For using it the file must be located on the server host., you must specify the full path of the file. But for using this function you must have FILE privilege and the file size is less than max_allowed_packet bytes.

mysql> SELECT LOAD_FILE('C:/MySQL/MySQL Server
5.0/data');

LOCATE(substr,str), LOCATE(substr,str,pos)The LOCATE(substr,str) function is same as INSTR(str, substr).
LOCATE(substr,str,pos) function is also same but its just start the to
find first occurrence of substr in String str from position pos.
These functions returns 0 if substr is not in String str.
Here are the some example of the LOCATE(substr,str), LOCATE(substr,str,pos)
function:Example:

LPAD(str,len,padstr)The LPAD(str, len, padstr) function returns the string str that is left padded with padstr string for length of len characters. But string str is longer
than len characters then return value is shortend to len characters. Example:

LTRIM(str)The LTRIM(str) function returns the string str with leading space characters removed.
Here are the some example of the LTRIM(str) function:Example:

mysql> SELECT LTRIM('
roseindia');
-> 'roseindia'

MAKE_SET(bits,str1,str2,...)The MAKE_SET(bits, str1, str2,..) function is returns a set value consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.
Here are the some example of the MAKE_SET(bits, str1, str2,..)
function:Example:

MID(str,pos,len)The MID(str, pos, len) function is same as SUBSTRING(str,pos,len)

OCT(N)The OCT(N) function is used to return a string representation of octal
value of N, here N is a longlong (BIGINT) number
Example:

mysql> SELECT OCT(12);
-> '14'

OCTET_LENGTH(str)The OCTET_LENGTH(str) function is same as LENGTH().

POSITION(substr IN str)
The POSITION(substr IN str) function is same as LOCATE (substr, str).

REPEAT (str, count)The REPEAT (str, count) function returns a string that consist a String str
repeated of count times. But if count time is
less than 1 than it returns an empty string.

mysql> SELECT REPEAT('Rose', 3);
->'RoseRoseRose'

REPLACE (str, from_str, to_str)The REPLACE (str, from_str, to_str) function returns the String str and in this String all occurrences of the
String from_str is replaced by the String to_str.
This function can perform a case-sensitive match when searching for from_str.

REVERSE(str)The REVERSE(str) function is used to return the reverse of String str.
Example:

mysql> SELECT REVERSE('123');
-> '321'

RIGHT(str, len)
The RIGET(str, len) function returns the rightmost len characters from the String str. It return NULL if any argument is NULL.
Example :

mysql> SELECT RIGHT ('Roseindia', 5);
->'india'

RPAD(str, len, padstr)
The RPAD(str, len, padstr) function returns the string str that is right padded with padstr string for length of len characters. But string str is longer than len characters then return value is shortend to len characters.Example :

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR
len) The first two functionsSUBSTRING(str,pos) and SUBSTRING(str FROM pos) return a substring from
str String that is started at position pos. And the other two functions
SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) return a substring, that?s length is
len characters, from String str and its started at position
pos.Example :