SQL String Data Type & Functions

Data type - Description

CHAR(size) - A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1

VARCHAR(size) - A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535

BINARY(size) - Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1

VARBINARY(size) - Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.

TINYBLOB - For BLOBs (Binary Large Objects). Max length: 255 bytes

TINYTEXT - Holds a string with a maximum length of 255 characters

TEXT(size) - Holds a string with a maximum length of 65,535 bytes

BLOB(size) - For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data

MEDIUMTEXT - Holds a string with a maximum length of 16,777,215 characters

MEDIUMBLOB - For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data

LONGTEXT - Holds a string with a maximum length of 4,294,967,295 characters

LONGBLOB - For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data

Function - Description

ASCII - Returns the ASCII value for the specific character

CHAR_LENGTH - Returns the length of a string (in characters)

CHARACTER_LENGTH - Returns the length of a string (in characters)

CONCAT - Adds two or more expressions together

CONCAT_WS - Adds two or more expressions together with a separator

FIELD - Returns the index position of a value in a list of values

FIND_IN_SET - Returns the position of a string within a list of strings

FORMAT - Formats a number to a format like “#,###,###.##”, rounded to a specified number of decimal places

INSERT - Inserts a string within a string at the specified position and for a certain number of characters

INSTR - Returns the position of the first occurrence of a string in another string

LCASE - Converts a string to lower-case

LEFT - Extracts a number of characters from a string (starting from left)

LENGTH - Returns the length of a string (in bytes)

LOCATE - Returns the position of the first occurrence of a substring in a string

LOWER - Converts a string to lower-case

LPAD - Left-pads a string with another string, to a certain length

LTRIM - Removes leading spaces from a string

MID - Extracts a substring from a string (starting at any position)

POSITION - Returns the position of the first occurrence of a substring in a string

REPEAT - Repeats a string as many times as specified

REPLACE - Replaces all occurrences of a substring within a string, with a new substring

REVERSE - Reverses a string and returns the result

RIGHT - Extracts a number of characters from a string (starting from right)

RPAD - Right-pads a string with another string, to a certain length

RTRIM - Removes trailing spaces from a string

SPACE - Returns a string of the specified number of space characters

STRCMP - Compares two strings

SUBSTR - Extracts a substring from a string (starting at any position)

SUBSTRING - Extracts a substring from a string (starting at any position)

SUBSTRING_INDEX - Returns a substring of a string before a specified number of delimiter occurs

TRIM - Removes leading and trailing spaces from a string

UCASE - Converts a string to upper-case

UPPER - Converts a string to upper-case