String operations

Find element in comma separated list

SELECT FIND_IN_SET('b','a,b,c');

Return value:

2

SELECT FIND_IN_SET('d','a,b,c');

Return value:

0

STR_TO_DATE - Convert string to date

With a column of one of the string types, named my_date_field with a value such as [the string] 07/25/2016, the following statement demonstrates the use of the STR_TO_DATE function:

SELECT STR_TO_DATE(my_date_field, '%m/%d/%Y') FROM my_table;

You could use this function as part of WHERE clause as well.

LOWER() / LCASE()

Convert in lowercase the string argument

Syntax: LOWER(str)

LOWER('fOoBar') -- 'foobar'
LCASE('fOoBar') -- 'foobar'

REPLACE()

Convert in lowercase the string argument

Syntax: REPLACE(str, from_str, to_str)

REPLACE('foobarbaz', 'bar', 'BAR') -- 'fooBARbaz'
REPLACE('foobarbaz', 'zzz', 'ZZZ') -- 'foobarbaz'

SUBSTRING()

SUBSTRING (or equivalent: SUBSTR) returns the substring starting from the specified position and, optionally, with the specified length

Syntax: SUBSTRING(str, start_position)

SELECT SUBSTRING('foobarbaz', 4); -- 'barbaz'
SELECT SUBSTRING('foobarbaz' FROM 4); -- 'barbaz'

-- using negative indexing
SELECT SUBSTRING('foobarbaz', -6); -- 'barbaz'
SELECT SUBSTRING('foobarbaz' FROM -6); -- 'barbaz'

Syntax: SUBSTRING(str, start_position, length)

SELECT SUBSTRING('foobarbaz', 4, 3); -- 'bar'
SELECT SUBSTRING('foobarbaz', FROM 4 FOR 3); -- 'bar'

-- using negative indexing
SELECT SUBSTRING('foobarbaz', -6, 3); -- 'bar'
SELECT SUBSTRING('foobarbaz' FROM -6 FOR 3); -- 'bar'

UPPER() / UCASE()

Convert in uppercase the string argument

Syntax: UPPER(str)

UPPER('fOoBar') -- 'FOOBAR'
UCASE('fOoBar') -- 'FOOBAR'

LENGTH()

Return the length of the string in bytes. Since some characters may be encoded using more than one byte, if you want the length in characters see CHAR_LENGTH()

Syntax: LENGTH(str)

LENGTH('foobar') -- 6
LENGTH('fööbar') -- 8 -- contrast with CHAR_LENGTH(...) = 6

CHAR_LENGTH()

Return the number of characters in the string

Syntax: CHAR_LENGTH(str)

CHAR_LENGTH('foobar') -- 6
CHAR_LENGTH('fööbar') -- 6 -- contrast with LENGTH(...) = 8

HEX(str)

Convert the argument to hexadecimal. This is used for strings.

HEX('fööbar') -- 66F6F6626172 -- in "CHARACTER SET latin1" because "F6" is hex for ö
HEX('fööbar') -- 66C3B6C3B6626172  -- in "CHARACTER SET utf8 or utf8mb4" because "C3B6" is hex for ö

Parameters

|Name|Description |---|---|---|--- |ASCII()|Return numeric value of left-most character |BIN()|Return a string containing binary representation of a number |BIT_LENGTH()|Return length of argument in bits |CHAR()|Return the character for each integer passed |CHAR_LENGTH()|Return number of characters in argument |CHARACTER_LENGTH()|Synonym for CHAR_LENGTH() |CONCAT()|Return concatenated string |CONCAT_WS()|Return concatenate with separator |ELT()|Return string at index number |EXPORT_SET()|Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string |FIELD()|Return the index (position) of the first argument in the subsequent arguments |FIND_IN_SET()|Return the index position of the first argument within the second argument |FORMAT()|Return a number formatted to specified number of decimal places |FROM_BASE64()|Decode to a base-64 string and return result |HEX()|Return a hexadecimal representation of a decimal or string value |INSERT()|Insert a substring at the specified position up to the specified number of characters |INSTR()|Return the index of the first occurrence of substring |LCASE()|Synonym for LOWER() |LEFT()|Return the leftmost number of characters as specified |LENGTH()|Return the length of a string in bytes |LIKE|Simple pattern matching |LOAD_FILE()|Load the named file |LOCATE()|Return the position of the first occurrence of substring |LOWER()|Return the argument in lowercase |LPAD()|Return the string argument, left-padded with the specified string |LTRIM()|Remove leading spaces |MAKE_SET()|Return a set of comma-separated strings that have the corresponding bit in bits set |MATCH|Perform full-text search |MID()|Return a substring starting from the specified position |NOT LIKE|Negation of simple pattern matching |NOT REGEXP|Negation of REGEXP |OCT()|Return a string containing octal representation of a number |OCTET_LENGTH()|Synonym for LENGTH() |ORD()|Return character code for leftmost character of the argument |POSITION()|Synonym for LOCATE() |QUOTE()|Escape the argument for use in an SQL statement |REGEXP|Pattern matching using regular expressions |REPEAT()|Repeat a string the specified number of times |REPLACE()|Replace occurrences of a specified string |REVERSE()|Reverse the characters in a string |RIGHT()|Return the specified rightmost number of characters |RLIKE|Synonym for REGEXP |RPAD()|Append string the specified number of times |RTRIM()|Remove trailing spaces |SOUNDEX()|Return a soundex string |SOUNDS LIKE|Compare sounds |SPACE()|Return a string of the specified number of spaces |STRCMP()|Compare two strings |SUBSTR()|Return the substring as specified |SUBSTRING()|Return the substring as specified |SUBSTRING_INDEX()|Return a substring from a string before the specified number of occurrences of the delimiter |TO_BASE64()|Return the argument converted to a base-64 string |TRIM()|Remove leading and trailing spaces |UCASE()|Synonym for UPPER() |UNHEX()|Return a string containing hex representation of a number |UPPER()|Convert to uppercase |WEIGHT_STRING()|Return the weight string for a string