String Functions in MySQL

Arjun Panwar
Nov 27, 2011
7.7k
0
0
Article

String Functions in MySQL

ASCII (str)
In this function returns ASCII code value of the leftmost character of the String str and returns 0 if str is the empty string.
Returns NULL if str is NULL.

Example : SELECT ASCII('0');
SELECT ASCII('d');

BIT_LENGTH (str)
In the BIT_LENGTH(str) function return the String str length in bits forms.

Example: SELECT BIT_LENGTH('a');

CHAR(N,... [USING charset_name] )
CHAR(N,... [USING charset_name] ) function return a string consisting the character and given the integer value. This function skipped the NULL values.

Example: SELECT CHAR(77,121,83,81,'76');

CHAR_LENGTH(str)
In this 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.

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: SELECT CONCAT('In', 'd', 'ia');
SELECT CONCAT('my', NULL, 'ql');
SELECT CONCAT(10.3);

CONCAT_WS(separator str1, str2,....)
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: SELECT CONCAT_WS(',', ' Title', 'First name', 'Last Name');
SELECT CONCAT_WS(',', 'First name', NULL, 'Last Name');

CONV (N, from_base, to_base)
In the CONV (N, from_base, to_base) function is used to convert the number between different number bases. CONV returns a String that is representation of number N. It is 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: SELECT CONV('a',10,2);
SELECT CONV('6E',10,5);
SELECT CONV(-17,9,-25);

ELT(N,str1,str2,str3,...)
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: SELECT ELT(1, '9', '10', '11', '12');
SELECT ELT(4, '9', '10', '11', '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: SELECT EXPORT_SET(1,'Y','N',',',2);
SELECT EXPORT_SET(6,'1','0',',',2);

FIELD(str,str1,str2,str3,...)
In 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: SELECT FIELD ('AA', 'BB', 'AA', 'CC');

FIND_IN_SET(str,strlist)
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: SELECT FIND_IN_SET('2', '1,2,3,4');

FORMAT(X,D)
In 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.

Example: SELECT FORMAT(1235.14687, 3);

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.

Example: SELECT HEX(10);
SELECT HEX( 'abd');

INSERT(str,pos,len,newstr)
This 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: SELECT INSERT('helloindia',2,3,'net');
SELECT INSERT('helloindia',-1,3,'net');

INSTR(str,substr)
This function is used to return the position of first occurrence of substr SubString in str String.

Example: SELECT INSTR('helloindia','e');
SELECT INSTR('xe', 'helloindia');

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

Example: SELECT LOWER('HELLOINDIA');

LCASE(str)
The LCASE(str) function is same as LOWER() function

LEFT(str,len)
This function returns the leftmost len characters from the String str.

Example: SELECT LEFT('helloindia', 4);

LENGTH(str)
The LENGTH(str) function returns the length of the String str in bytes.

Example: SELECT LENGTH("helloindia");

LOAD_FILE(file_name)
This 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.

Example: 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.

Example: SELECT LOCATE('d','roseindia',4);

LPAD(str,len,padstr)
In 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: SELECT LPAD('hello',7,'??');

LTRIM(str)
LTRIM(str) function returns the string str with leading space characters removed.

Example: SELECT LTRIM(' helloindia');

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.

Example: SELECT MAKE_SET(2,'a', 'b','c','d');
             SELECT MAKE_SET(1|2,'hello','nice','comp');
             SELECT MAKE_SET(1|4,'good','nice',null,'by');
             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: SELECT OCT(12);

OCTET_LENGTH(str)
The OCTET_LENGTH(str) function is similair to LENGTH().

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

REPEAT (str, count)
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.

Example: SELECT REPEAT('Hello', 3);

REPLACE (str, from_str, to_str)
In 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.

Example: SELECT REPLACE ('www.mcnsolutions.net', 'w', 'W');

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

Example: SELECT REVERSE('123');

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

Example : SELECT RIGHT ('Helloindia', 5);

RPAD(str, len, padstr)
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 : SELECT RPAD ('hello', 7, '?');

RTRIM(str)
The RTRIM(str) function returns the String str with trailing space characters removed.

Example : SELECT RTRIM ('hello ');

SPACE(N)
In the SPACE(N) function returns a String that consist of N space characters.

Example :SELECT SPACE(5);

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
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 :SELECT SUBSTRING('HelloIndia',5);
SELECT SUBSTRING('HelloIndia' FROM 5);
SELECT SUBSTRING('HelloIndia',5,3);