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

user_8196559 0
Viewed 193

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?

mysql-error-1064 mysql
1 Answers

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
user_106767160
Related