MySQL ASCII() String Function

MySQL ASCII() String Function

The MySQL ASCII() string function returns the ASCII value of the leftmost character of a string. This function is essential for retrieving the numeric ASCII code of a character in SQL queries.

Syntax

SELECT ASCII(string) AS result
FROM table_name;

The ASCII() function has the following components:

  • string: The string whose leftmost character's ASCII value is to be returned.
  • result: An alias for the resulting ASCII value.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL ASCII() String Function

Let's look at some examples of the MySQL ASCII() string function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50)
);

This query creates a table named employees with columns for id, first_name, last_name, and department.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department)
VALUES ('John', 'Doe', 'HR'),
       ('Jane', 'Smith', 'IT'),
       ('Jim', 'Brown', 'IT'),
       ('Jake', 'White', 'HR'),
       ('Jill', 'Green', 'Marketing');

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using ASCII() with WHERE Clause

Use the ASCII() function to retrieve the ASCII value of the leftmost character of a string:

SELECT first_name, ASCII(first_name) AS ascii_value
FROM employees;

This query retrieves the first_name column from the employees table and returns the ASCII value of the leftmost character of first_name.

MySQL ASCII() WITH WHERE CLAUSE

Step 5: Using ASCII() with Multiple Columns

Use the ASCII() function with multiple columns:

SELECT first_name, last_name, ASCII(first_name) AS first_name_ascii, ASCII(last_name) AS last_name_ascii
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the ASCII value of the leftmost character of both first_name and last_name.

MySQL ASCII() WITH MULTIPLE COLUMNS

Step 6: Using ASCII() with Constants

Use the ASCII() function with constants:

SELECT ASCII('John') AS john_ascii, ASCII('Jane') AS jane_ascii
FROM employees;

This query retrieves the ASCII value of the leftmost character of the constant strings 'John' and 'Jane'.

MySQL ASCII() WITH CONSTANTS

Conclusion

The MySQL ASCII() function is a powerful tool for retrieving the ASCII value of the leftmost character of a string in SQL queries. Understanding how to use the ASCII() function is essential for effective data querying and analysis in MySQL.