String manipulating group of functions are a very important and very often used part of MySQL pre-defined functions. The following sub-chapters show a most common string functions in MySQL.

BIN(N)

The BIN function returns a string representation of binary value of BIGINT number (N). In the example below after number '50' is being passed to the function, it returns 110010.

SELECT BIN(50);

BIT_LENGTH(str)

The LENGTH() function returns the length of an input string in form of bits. The example below returns the length of 'Ryan' in bits as 32.

SELECT BIT_LENGTH('Ryan');

CHAR_LENGTH(str)

The CHAR_LENGTH() function returns the length of an input string in form of char. The example below returns the length of 'Ryan' in bits as 4.

SELECT CHAR_LENGTH('Ryan');

CHAR(N1,...Nn)

The CHAR() function returns the string for the code values of an integer passed to the function. The example given below outputs "ABC".

SELECT CHAR(65,66,67);

CONCAT_WS(separator, str1, str2 ...)

The CONCAT_WS() function concatenates an input strings with a separator value. The example given below produces the output as "INSERT/UPDATE/DELETE".

SELECT CONCAT_WS("/", "INSERT","UPDATE", "DELETE");

CONCAT(str1, str2 ...)

The CONCAT() function concats input strings and returns the resultant string. The example below gives the output as "MySchool".

SELECT CONCAT("My", "School");

ELT(N, str1, str2 ...)

The ELT() function returns a string at the index number. The example gives the output as "My".

SELECT ELT(1 , "My", "School");

HEX(value)

The HEX() function returns a string representation of hexadecimal value of the decimal or string value sent as argument. In example below the input number '56' produces hexadecimal equivalent of '38'. For input value 'A' output is '41'.

SELECT HEX(56);

SELECT HEX("A");

INSERT(str,pos,len,newstr)

The INSERT() function inserts into a string "str" a new string "newstr" which is of length "len" at the position "pos". The statement below produces output as "ring-ah-s".

SELECT INSERT("ringroses",5, 4,"-ah-");

INSTR(str,substr)

The INSTR() function returns the position of the first occurrence of the substring in "str". The example below produces output 6.

SELECT INSTR('ring-Ah-ring-Ah-roses', 'Ah');

LCASE(str)

The LCASE() function returns the lower case of an input string. The output for the below syntax is "abc".

SELECT LCASE("ABC");

OCT(N)

The OCT() function gives a string representation of the octal value N, where N is BIGINT number. The output for below statement is 62.

SELECT OCT(50);

REPEAT(str, count)

The REPEAT() function repeats a string "str" for number of times in "count" and returns the string. The example below gives the output as "tickticktick".

SELECT REPEAT('tick', 3);

REPLACE (str, from_str, to_str)

The REPLACE() function replaces the string passed in "from_str" to the string in "to_str" in an input string "str". The example below gives the output as "Sahana Shewtha vikaS".

SELECT REPLACE("sahana shewtha vikas", "s", "S");

REVERSE(str)

The REVERSE() function returns the reverse string of an input string. The syntax below produces output as "raggeb".

SELECT REPLACE("beggar");

SPACE(N)

The SPACE() function introduces "N" empty spaces into a string. The output of below expression is ' '.

SELECT SPACE(4);

STRCMP(str1, str2)

The STRCMP() function compares two strings and if they are equal it returns 0, if first argument is smaller it returns -1, else returns 1. The output for below example is 1.

SELECT STRCMP("see", "saw");

SUBSTR(str, pos) | SUBSTR(str, pos, len) | SUBSTR(str FROM pos) | SUBSTR(str FROM pos FOR len)

The SUBSTR() function is a synonym of SUBSTRING(). This methods returns substring from "str" from the position "pos". If the length of string to be returned is "len", the part of the string is returned. The output of statement below is "heaven".

SELECT SUBSTRING("I see a heaven coming..." FROM 9 FOR 6);

TRIM(" bar ") | TRIM(LEADING 'ch' FROM 'chchstr') | TRIM(TRAILING 'ch' FROM 'strchch') | TRIM(BOTH 'ch' FROM 'chchstrchch')

The TRIM() function removes the leading and trailing or both characters from a string. The output for below statement is "ero".

SELECT TRIM(LEADING 'h' FROM "hhhhhero");

UPPER()

The UPPER() function converts an input string to all uppercase characters. The example below gives the output as "HEROINE".

SELECT UPPER("heroine");