Returning the DISTINCT first character of a field (MySQL)

Question

I would like to produce a character list of all of the first letters of column in my database. The SQL below illistrats what I would like to return.

SELECT DISTINCT first_character(name) FROM mydatabase

Is there a way to do this in MySQL?

EDIT What is the advantage of using SUBSTRING over LEFT and vice versa?

EDIT Currently there are about 1700 records in the table and growing.

Answer 1

Sounds simple:

select distinct substring(field,1,1) as char
from mytable

Answer 2

Sorry to do this, but I figured out exactly what I needed to do just now.

SELECT DISTINCT LEFT(name, 1) FROM mydatabase

This returned a list of the first, distinct, single characters that each row in the column started with. I added changed it to the following to get it the list in alpha-numeric order:

SELECT DISTINCT LEFT(name, 1) as letter FROM mydatabase ORDER BY letter

Works like a charm.

Answer 3

For your current table of 1,700 rows your solution is OK.

If you will have like 100,000 of rows, the DISTINCT may become inefficient.

Here's the article in my blog that shows how to do it efficiently:

• Selecting first letters

This solution employs an index on name. It will jump over the index keys, selecting each first letter at most once.

First, you'll need to create a function:

CREATE FUNCTION fn_get_next_code(initial INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _next VARCHAR(200);
        DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
        SELECT  ORD(SUBSTRING(name, 1, 1))
        INTO    _next
        FROM    t_names
        WHERE   name >= CHAR(initial + 1)
        ORDER BY
                name
        LIMIT 1;
        RETURN _next;
END

This function, given a code of a starting letter, returns the first starting letter next to the given from your table.

Second, use this function in a query:

SELECT  CHAR(@r) AS starting,
        @r := fn_get_next_letter(@r) AS next
FROM    (
        SELECT @r := ORD(LEFT(MIN(name), 1))
        ) vars, mytable
WHERE   @r IS NOT NULL

On each iteration, session variable @r will skip to the next starting letter using an index.

This will be very fast, but it pays for itself only if you have hundreds of thousands of rows.

Otherwise just use DISTINCT.