Example
Return the length of the string:
SELECT CHAR_LENGTH("SQL Tutorial") AS LengthOfString;
Category: SQL
MySQL ASCII() Function
Example
Return the ASCII value of the first character in “CustomerName”:
SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;
MySQL Functions
MySQL has many built-in functions.
This reference contains string, numeric, date, and some advanced functions in MySQL.
MySQL String Functions
| 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 |
SQL FROM Keyword
FROM
The FROM command is used to specify which table to select or delete data from.
The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table:
Example
SELECT CustomerName, City FROM Customers;
The following SQL statement selects all the columns from the “Customers” table: Continue reading SQL FROM Keyword
SQL FOREIGN KEY Keyword
FOREIGN KEY
The FOREIGN KEY constraint is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
SQL EXISTS Keyword
EXISTS
The EXISTS command tests for the existence of any record in a subquery, and returns true if the subquery returns one or more records.
The following SQL lists the suppliers with a product price less than 20:
Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
The following SQL lists the suppliers with a product price equal to 22: Continue reading SQL EXISTS Keyword
SQL EXEC Keyword
EXEC
The EXEC command is used to execute a stored procedure.
The following SQL executes a stored procedure named “SelectAllCustomers”:
Example
EXEC SelectAllCustomers;
SQL DROP VIEW Keyword
DROP VIEW
The DROP VIEW command deletes a view.
The following SQL drops the “Brazil Customers” view:
Example
DROP VIEW [Brazil Customers];
SQL DROP TABLE and TRUNCATE TABLE Keywords
DROP TABLE
The DROP TABLE command deletes a table in the database.
The following SQL deletes the table “Shippers”:
Example
DROP TABLE Shippers;
Note: Be careful before deleting a table. Deleting a table results in loss of all information stored in the table!
SQL DROP INDEX Keyword
DROP INDEX
The DROP INDEX command is used to delete an index in a table.
MS Access:
DROP INDEX index_name ON table_name;
SQL Server:
DROP INDEX table_name.index_name;