How to select char_length of (select ...) in mysql

Question

I have to select char length of string which I can get by another select.

SELECT DISTINCT(TABLE_SCHEMA) FROM INFORMATION_SCHEMA.TABLES LIMIT 0, 1

returns

TABLE_SCHEMA
mysql

Now I want to get char_length of this mysql and make it in one query.

These queries

SELECT CHAR_LENGTH(SELECT DISTINCT(TABLE_SCHEMA) FROM INFORMATION_SCHEMA.TABLES LIMIT 0, 1)
SELECT CHAR_LENGTH(DISTINCT(TABLE_SCHEMA) FROM INFORMATION_SCHEMA.TABLES LIMIT 0, 1)

return syntax error.

So how to this create query?

Answer 1

To start with: if you a just looking for the list of databases, you want to use information_schema.schemata instead of information_schema.tables. This avoids the need to use DISTINCT, and then you can simply do:

SELECT schema_name, CHAR_LENGTH(schema_name) schema_name_length
FROM information_schema.schemata
LIMIT 0, 1

NB: LIMIT without an ORDER BY clause is not a good practice. You cannot predict which records will come up first.

---

If for some reason you do want to stick to your current query, then you would need to turn it to a subquery, as follows:

SELECT TABLE_SCHEMA, CHAR_LENGTH(TABLE_SCHEMA) TABLE_SCHEMA_LENGTH
FROM (
    SELECT DISTINCT(TABLE_SCHEMA) FROM INFORMATION_SCHEMA.TABLES LIMIT 0, 1
) x