Home Mysql Mysql5 MySQL String Function

MySQL String Function
Posted on: March 20, 2008 at 12:00 AM

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

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:

mysql> SELECT ASCII('0');
-> 48
mysql> SELECT ASCII(0);
-> 48
mysql> SELECT ASCII('d');
-> 100

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:

mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(66,66.3,'66.3');
-> 'BBB'

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:

mysql> SELECT CONCAT('In', 'd', 'ia');
-> 'India'
mysql> SELECT CONCAT('my', NULL, 'ql');
-> NULL
mysql> SELECT CONCAT(10.3);
-> '10.3'

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:

mysql> SELECT CONCAT_WS(',', ' Title', 'First name', 'Last Name');
-> 'Title, First name, Last Name'
mysql> SELECT CONCAT_WS(',', 'First name', NULL, 'Last Name');
-> 'First name, Last Name'

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:

mysql> SELECT CONV('a',10,2);
-> '0'
mysql> SELECT CONV('6E',10,5);
-> '11'
mysql> SELECT CONV(-17,9,-25);
-> '-G'
mysql> SELECT CONV(10+'10'+'10'+0xa,10,5);
-> '130'

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:


mysql> SELECT ELT(1, '9', '10', '11', '12');
-> '9'
mysql> SELECT ELT(4, '9', '10', '11', '12');
-> '12'
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:

mysql> SELECT EXPORT_SET(1,'Y','N',',',2);
-> 'Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',2);
-> '0,1'

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:

mysql> SELECT FIELD ('AA', 'BB', 'AA', 'CC');
-> 2
mysql> SELECT FIELD ('AA', 'BB', 'CC', 'DD');
-> 0
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:

mysql> SELECT FORMAT(1235.14687, 3);
-> 1,235.147
mysql> SELECT FORMAT(145678.1,2);
-> 145,678.10
mysql> SELECT FORMAT(24567.1,0);
-> 24567

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:

mysql> SELECT INSERT('roseindia',2,3,'net');
-> rnetindia
mysql> SELECT INSERT('roseindia',-1,3,'net');
-> roseindia
mysql> SELECT INSERT('roseindia',3,100,'net');
->ronet

INSTR(str,substr)
The INSTR(str, substr) function is used to return the position of first occurrence of substr SubString in str String. Here are some example of the INSTR(str,substr) function:
Example:

mysql> SELECT INSTR('roseindia','e');
-> '4'
mysql> SELECT INSTR('xe', 'roseindia');
-> '0'

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 is same 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:

mysql> SELECT LOCATE('in','roseindia');
-> 5
mysql> SELECT LOCATE('xin','roseindia');
-> 0
mysql> SELECT LOCATE('d','roseindia',4);
-> 7

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:

mysql> SELECT LPAD('hello',7,'??');
->??hello
mysql> SELECT LPAD('hello',1,'??');
-> h
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:

mysql> SELECT MAKE_SET(2,'a', 'b','c','d');
-> 'b'
mysql> SELECT MAKE_SET(1|2,'hello','nice','comp');
-> 'hello,nice'
mysql> SELECT MAKE_SET(1|4,'good','nice',null,'by');
-> 'good'
mysql> SELECT MAKE_SET(0, '1','2', '3', '4');
->''

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.

mysql> SELECT REPLACE ('www.roseindia.net', 'w', 'W');
->'WWW.roseindia.net';
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 :

mysql> SELECT RPAD ('rose', 7, '?');
->'rose???'
RTRIM(str)
The RTRIM(str) function returns the String str with trailing space characters removed.
Example :

mysql> SELECT RTRIM ('rose ');
->'rose'
SPACE(N)
The SPACE(N) function returns a String that consist of N space characters.
Example :

mysql> SELECT SPACE(5);
-> ' '

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
The first two functions SUBSTRING(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 :

mysql> SELECT SUBSTRING('RoseIndia',5);
-> 'India'
mysql> SELECT SUBSTRING('RoseIndia' FROM 5);
-> 'India'
mysql> SELECT SUBSTRING('RoseIndia',5,3);
-> 'Ind'

UPPER(str)
The UPPER(str) function return the String str. And in this string all the characters are changed in the uppercase.
Example :

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

Related Tags for MySQL String Function:
sql, mysql, c, string, com, data, functions, function, fun, io, sed, compile, ip, port, pil, this, support, unc, mpi, to, learn, ear, e, il, rte, section, can, use, ul, man, in, compiled, m, out, tr, os, ca, os, es, func, all, alm, pi, pp, s, su, at, my, late, is, ha, ll, ea, ar, str, rt, uses, s, s, ri, ring, th, av, st, ab, hat, most, on, om, o

«Previous Index Next»