MySQL String Functions - slides presentation

Transcript

ASCII()

This function returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL
Syntax : ASCII(str)
Example : SELECT ASCII('2');
Output : 50
Example : SELECT ASCII(2);
Output : 50
Example : SELECT ASCII(‘An’);
Output : 65

BIN()

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. Returns NULL if N is NULL.
Syntax : BIN(N)
Example : SELECT BIN(12);
Output : 1100

BIT_LENGTH()

Returns the length of the string str in bits.
Syntax : BIT_LENGTH(str)
Example : SELECT BIT_LENGTH('text');
Output : 32

CHAR()

CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.
Syntax : CHAR(N,... [USING charset_name])
Example : SELECT CHAR(77,121,83,81,'76');
Output : MySQL
Example : SELECT CHAR(77,77.3,'77.3');
Output : MMM

CHAR_LENGTH()

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
Syntax : CHAR_LENGTH(str)
Example : SELECT CHAR_LENGTH('test string');
Output : 11

CONCAT()

Returns the string that results from concatenating one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.
Syntax : CONCAT(str1,str2,...)
Example : SELECT CONCAT('w3resource','.','com');
Output : w3resource.com

CONCAT_WS()

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.
Syntax : CONCAT_WS(separator,str1,str2,...)
Example : SELECT CONCAT_WS(',','1st string','2nd string');
Output : 1st string,2nd string

ELT()

ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().
Syntax : ELT(N,str1,str2,str3,...)
Example : SELECT ELT(4,'this','is','the','elt');
Output : elt

EXPORT_SET()

Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string.
Syntax : EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
Example : SELECT EXPORT_SET(5,'Y','N',',',3);
Output : Y,N,Y

FIELD()

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.
Syntax : FIELD(str,str1,str2,str3,...)
Example : SELECT FIELD('ank', 'b', 'ank', 'of', 'monk');
Output : 2

FIND_IN_SET()

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters.
Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.
Syntax : FIND_IN_SET(str,strlist)
Example : SELECT FIND_IN_SET('ank','b,ank,of,monk');
Output : 2

FORMAT()

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.
Syntax : FORMAT(X,D)
Example : SELECT FORMAT(12332.123456, 4);
Output : 12,332.1235
Example : SELECT FORMAT(12332.1,4);
Output : 12,332.1000
Example : SELECT FORMAT(12332.2,0);
Output : 12,332

HEX()

MySQL HEX() returns a string representation of hexadecimal value of a decimal or string value specified as argument.
If the argument is a string, each character in the argument is converted to two hexadecimal digits.
If the argument is decimal, the function returns a hexadecimal string representation of the argument , and treated as a longlong(BIGINT) number.
Syntax : HEX(str), HEX(N)
Example : SELECT HEX(157);
Output : 9D

INSERT()

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string.
Syntax : INSERT(str,pos,len,newstr)
Example : SELECT INSERT('Originalstring', 4, 5, ' insert ');
Output : Ori insert string
Example : SELECT INSERT('Originalstring', -3, 5, ' insert ');
Output : Originalstring

INSTR()

MySQL INSTR() takes a string and a substring of it as arguments, and returns an integer which indicates the position of the first occurrence of the substring within the string
Syntax : INSTR(str,substr)
Example : SELECT INSTR('myteststring','st');
Output : 5

LCASE()

MySQL LCASE() converts the characters of a string to lower case characters.
Syntax : LCASE(str)
Example : SELECT LCASE('MYTESTSTRING');
Output : myteststring

LEFT()

MySQL LEFT() returns a specified number of characters from the left of a given string. Both the number and the string are supplied in the arguments as str and len of the function.
Syntax : LEFT(str,len)
Example : SELECT LEFT('w3resource', 3);
Output : w3r

LENGTH()

MySQL LENGTH() returns the length of a given string.
Syntax : LENGTH(str)
Example : SELECT LENGTH('text');
Output : 4

LOCATE()

MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not mentioned, searching starts from the beginning.
Syntax : LOCATE(substr,str,pos)
Example : SELECT LOCATE('st','myteststring');
Output : 5

LOWER()

MySQL LOWER() converts all the characters in a string to lowercase characters.
Syntax : LOWER(str)
Example : SELECT LOWER('MYTESTSTRING');
Output : myteststring

LPAD()

MySQL LPAD() left pads a string with another string. The actual string, a number indicating the length of the padding in characters (optional) and the string to be used for left padding - all are passed as arguments.
Syntax : LPAD(str,len,padstr)
Example : SELECT LPAD('Hello',10,'**');
Output : *****Hello
Example : SELECT LPAD('hi',1,'**');
Output : h

LTRIM(str)

MySQL LTRIM() removes the leading space characters of a string passed as argument.
Syntax : LTRIM(str)
Example : SELECT LTRIM(' Hello')
Output : Hello ( leading spaces have been exclude)

MAKE_SET()

MySQL MAKE_SET() returns a set value (a string containing substrings separated by “,” characters) consisting of the strings that have the corresponding bit in the first argument.
Syntax : MAKE_SET(bits,str1,str2,...)
Example : SELECT MAKE_SET(1,'a','b','c');
Output : a
Example : SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
Output : hello

MID()

MySQL MID() extracts a substring from a string. The actual string, position to start extraction and length of the extracted string - all are specified as arguments.
Syntax : MID(str,pos,len)
Example : SELECT MID('w3resource',4,3);
Output : eso

OCT()

Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. Returns NULL if N is NULL.
Syntax : OCT(N)
Example : SELECT OCT(12);
Output : 14

ORD()

MySQL ORD() returns the code for the leftmost character if that character is a multi-byte (sequence of one or more bytes) one. If the leftmost character is not a multibyte character, ORD() returns the same value as the ASCII() function.
Syntax : ORD(str)
Example : SELECT ORD("w3resource");
Output : 119

POSITION()

MySQL POSITION() returns the position of a substring within a string..
Syntax : POSITION(substr IN str)
Example : SELECT POSITION("ou" IN "w3resource");
Output : 6

QUOTE()

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (“\”), single quote (“'”), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.
Syntax : QUOTE(str)
Example : SELECT QUOTE('w3re''source');
Output : 'w3re\'source'

REPEAT()

MySQL REPEAT() repeats a string for a specified number of times.
The function returns NULL either any either of the arguments are NULL.
Syntax : REPEAT(str,count)
Example : SELECT REPEAT('**-',5);
Output : **-**-**-**-**-

REPLACE()

MySQL REPLACE() replaces all the occurrences of a substring within a string.
Syntax : REPLACE(str,from_str,to_str)
Example : SELECT REPLACE('w3resource','ur','r');
Output : w3resorce

REVERSE()

Returns a given string with the order of the characters reversed.
Syntax : REVERSE(str)
Example : SELECT REVERSE('w3resource');
Output : ecruoser3w

RIGHT()

MySQL RIGHT() extracts a specified number of characters from the right side of a given string.
Syntax : RIGHT(str,len)
Example : SELECT RIGHT('w3resource',8);
Output : resource

RPAD()

MySQL RPAD() function pads strings from right. The actual string which is to be padded as str, length of the string returned after padding as len and string which is used for padding as padstr is used as a parameters within the argument.
Syntax : RPAD(str,len,padstr)
Example : SELECT RPAD('w3resource',15,'*');
Output : w3resource*****

RTRIM()

MySQL RTRIM() removes the trailing spaces from a given string.
Syntax : RTRIM(str)
Example : SELECT RTRIM('w3resource ');
(excludes the trailing spaces) Output : w3resource

SOUNDEX()

MySQL SOUNDEX() function returns soundex string of a string.
Soundex is a phonetic algorithm for indexing names after English pronunciation of sound. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.
Syntax : SOUNDEX(str)
Example : SELECT SOUNDEX('w3resource');
Output : w6262

SPACE()

MySQL SPACE() returns the string containing a number of spaces as specified in the argument.
Syntax : SPACE(N)
Example : SELECT 'start', SPACE(10), 'end';
Output : start end SPACE(10)

SUBSTR()

MySQL SUBSTR() returns the specified number of characters from a particular position of a given string. SUBSTR() is a synonym for SUBSTRING().
Syntax : SUBSTR(str,pos,len)
Example : SELECT SUBSTR('w3resource',4,3);
Output : eso

SUBSTRING()

MySQL SUBSTRING() returns a specified number of characters from a particular position of a given string.
Syntax : SUBSTRING(str,pos,len)
Example : SELECT SUBSTRING('w3resource',4,3);
Output : eso
Example : SELECT SUBSTRING('w3resource.com',5);
Output : source.com
Example : SELECT SUBSTRING('w3resource.com',-5);
Output : e.com

SUBSTRING_INDEX()

MySQL SUBSTRING_INDEX() returns the substring from the given string before a specified number of occurrences of a delimiter.
Returns from the left of the final delimiter if the number is positive and right of the final delimiter when the number is negative.
If the number is greater than the number of occurrence of delimiter, the returned substring will be the total string. If the specified number is 0, nothing will be fetched from the given string.
Syntax : SUBSTRING_INDEX(str,delim,count)
Example : SELECT SUBSTRING_INDEX('www.mytestpage.info','.',2);
Output : www.mytestpage

TRIM()

MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string.
Syntax : TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
Example : SELECT TRIM(' trim ');
Output : trim (leading and trailing space removed)
Example : SELECT TRIM(LEADING 'leading' FROM 'leadingtext' );
Output : text
Example : SELECT TRIM(BOTH 'leadtrail' FROM 'leadtrailtextleadtrail');
Output : text

UNHEX()

MySQL UNHEX() function performs the opposite operation of HEX(). This function interprets each pair of hexadecimal digits (in the argument) as a number and converts it to a character.
Syntax : UNHEX(str)
Example : SELECT UNHEX('4D7953514C');
Output : MySQL
Example : SELECT UNHEX(HEX('MySQL'));
Output : MySQL

UPPER()

MySQL UPPER() converts all the characters in a string to uppercase characters.
Syntax : UPPER(str)
Example : SELECT UPPER('myteststring');
Output : MYTESTSTRING