\n\u00a0mysql> SELECT BIN(5); -> ‘101’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<\/ul>\n\n- BIT_LENGTH (str)
\n<\/strong>The BIT_LENGTH(str) function return the String str<\/strong><\/em> length in bits . Here are the some example of the BIT_LENGTH(str) function:<\/p>\n\n\n\nmysql> SELECT BIT_LENGTH(‘a’); -> 8<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<\/ul>\n\n- CHAR(N,… [USING charset_name] )
\n<\/strong>The CHAR(N,… [USING charset_name] ) function\u00a0 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:\u00a0 Example:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT CHAR(77,121,83,81,’76’); -> ‘MySQL’ mysql> SELECT CHAR(66,66.3,’66.3′); -> ‘BBB’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<\/ul>\n\n- CHAR_LENGTH(str)
\n<\/strong>The CHAR_LENGTH(str) function returns String str<\/em><\/strong> 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.<\/li>\n- CHARACTER_LENGTH(str)<\/strong>
\nThis function is same as CHAR_LENGTH().<\/li>\n- CONCAT(str1, str2..)
\n<\/strong>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.
\n<\/strong> Example:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT CONCAT(‘In’, ‘d’, ‘ia’); -> ‘India’ mysql> SELECT CONCAT(‘my’, NULL, ‘ql’); -> NULL mysql> SELECT CONCAT(10.3); -> ‘10.3’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- CONCAT_WS(separator str1, str2,….)
\n<\/strong>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:<\/strong><\/p>\n\n\n\nmysql> 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’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- CONV (N, from_base, to_base)
\n<\/strong>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<\/strong><\/em> to base to_base<\/strong><\/em>. 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<\/strong><\/em> is negative then N is treated as a signed number else it is treated as a unsigned number.
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> 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’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- ELT(N,str1,str2,str3,…)
\n<\/strong>The ELT(N, str1, str2, str3,..) function returns str1<\/em><\/strong> if N=1 and str2<\/strong><\/em> 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:
\nExample:<\/strong><\/p>\n\n\n\nmysql> SELECT ELT(1, ‘9’, ’10’, ’11’, ’12’); -> ‘9’ mysql> SELECT ELT(4, ‘9’, ’10’, ’11’, ’12’); -> ’12’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- EXPORT_SET(bits,on,off[,separator[,number_of_bits]])<\/strong>
\nThe EXPORT_SET(bits, on, off[, separator[,number_of_bits]]) function returns a string for a every bit set in the value bits<\/strong><\/em>, then you get the on<\/strong><\/em> String but for every reset bit you can get the off<\/strong><\/em> string. In this function Bits in bits<\/strong><\/em> 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<\/strong><\/em> that?s default value is 64.
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT EXPORT_SET(1,’Y’,’N’,’,’,2); -> ‘Y,N’ mysql> SELECT EXPORT_SET(6,’1′,’0′,’,’,2); -> ‘0,1’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<\/ul>\n\n- FIELD(str,str1,str2,str3,…)
\n<\/strong> The FIELD(str,str1,str2,str3,….) function is used to find the index position of str<\/strong><\/em> in the arguments str1,str2,str3<\/em><\/strong>. In other words it returns the index position of str<\/strong><\/em> in the arguments. It returns 0 if str<\/strong><\/em> is not available in the arguments. If str<\/strong><\/em> is NULL then return value is 0 because NULL fails equality comparison with any value.
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT FIELD (‘AA’, ‘BB’, ‘AA’, ‘CC’); -> 2 mysql> SELECT FIELD (‘AA’, ‘BB’, ‘CC’, ‘DD’); -> 0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- FIND_IN_SET(str,strlist)<\/strong>
\nThe FIND_IN_SET(str, strlist) function returns a value in the range of 1 to N. This function find the String str<\/strong><\/em> in the substring of String list strlist<\/strong><\/em> and return the index value. This String list have many substrings that is separated by ?,? characters. This function returns 0 when str<\/strong><\/em> is not available in stringlist or string list is the empty string.
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT FIND_IN_SET(‘2’, ‘1,2,3,4’); -> 2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- FORMAT(X,D)
\n<\/strong>The FORMAT(X,D) function formats the number X<\/strong> like #,###,###.## and rounded the decimal places to D<\/strong> 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 :
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT FORMAT(1235.14687, 3); -> 1,235.147 mysql> SELECT FORMAT(145678.1,2); -> 145,678.10 mysql> SELECT FORMAT(24567.1,0); -> 24567<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- HEX(N_or_S)
\n<\/strong>In HEX(N_or_S) function N_or_S<\/strong><\/em> 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<\/strong><\/em> is a string, then it returns a string hexadecimal representation of N_or_S<\/strong><\/em> where each character in N_or_S<\/strong><\/em> is converted to two hexadecimal digits. Here are some example of the HEX(N_or_S) function are given below:
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT HEX(10); -> ‘A’ mysql> SELECT HEX( ‘abd’); -> 616264<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- INSERT(str,pos,len,newstr)
\n<\/strong>The INSERT(str, pos, len, newstr) function is used to replace some part or whole String of String str<\/strong><\/em> with String newstr<\/strong><\/em> from beginning at position pos<\/em><\/strong> and len<\/strong><\/em> character long. This function returns the String str<\/strong><\/em> if pos<\/strong><\/em> is not within the length of the string. It returns NULL if any argument is NULL. Example:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT INSERT(‘roseindia’,2,3,’net’); -> rnetindia mysql> SELECT INSERT(‘roseindia’,-1,3,’net’); -> roseindia mysql> SELECT INSERT(‘roseindia’,3,100,’net’); ->ronet<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- INSTR(str,substr)
\n<\/strong>The INSTR(str, substr) function is used to return the position of first occurrence of substr<\/em><\/strong> SubString in str<\/strong><\/em> String. Here are some example of the INSTR(str,substr) function: Example:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT INSTR(‘roseindia’,’e’); -> ‘4’ mysql> SELECT INSTR(‘xe’, ‘roseindia’); -> ‘0’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<\/ul>\n\n- LOWER(str)<\/strong>
\nThe LOWER(str) function return the String str<\/strong><\/em>. And in this String all the characters are changed in the lowercase.<\/p>\n\n\n\nmysql> SELECT LOWER(‘ROSEINDIA’); ->’roseindia’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- LCASE(str)
\n<\/strong>The LCASE(str) function is same as LOWER() function<\/li>\n- LEFT(str,len)
\n<\/strong>The LEFT(str, len) function returns the leftmost len<\/em><\/strong> characters from the String str<\/em><\/strong>. Here are the some example of the LEFT(str, len) function:
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT LEFT(‘roseindia’, 4); -> ‘rose’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<\/ul>\n\n- LENGTH(str)
\n<\/strong>The LENGTH(str) function returns the length of the String str<\/em><\/strong> in bytes. Here are the some example of the LENGTH(str) function:
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT LENGTH(“roseindia”); ->’9′<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- LOAD_FILE(file_name)
\n<\/strong>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.<\/p>\n\n\n\nmysql> SELECT LOAD_FILE(‘C:\/MySQL\/MySQL Server 5.0\/data’);<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- LOCATE(substr,str), LOCATE(substr,str,pos)
\n<\/strong>The LOCATE(substr,str) function is same as INSTR(str, substr). LOCATE(substr,str,pos) function is\u00a0also same but its just start the to find first occurrence of substr in String str<\/em><\/strong> from position pos<\/em><\/strong>. These functions returns 0 if substr is not in String str<\/em><\/strong>. Here are the some example of the LOCATE(substr,str), LOCATE(substr,str,pos) function:
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT LOCATE(‘in’,’roseindia’); -> 5 mysql> SELECT LOCATE(‘xin’,’roseindia’); -> 0 mysql> SELECT LOCATE(‘d’,’roseindia’,4); -> 7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- LPAD(str,len,padstr)
\n<\/strong>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.
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT LPAD(‘hello’,7,’??’); ->??hello mysql> SELECT LPAD(‘hello’,1,’??’); -> h<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- LTRIM(str)
\n<\/strong>The LTRIM(str) function returns the string str<\/em> with leading space characters removed. Here are the some example of the LTRIM(str) function:
\nExample:
\n<\/strong><\/p>\n\n\n\nmysql> SELECT LTRIM(‘\u00a0 roseindia’); -> ‘roseindia’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n- MAKE_SET(bits,str1,str2,…)
\n<\/strong>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\u00a0 the MAKE_SET(bits, str1, str2,..) function: Example:
\n<\/strong><\/p>\n\n\n\n| 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’); ->”<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n | | | | | | | | | | | | | | | | | | | | | |