CODEWITHSUNDEEP
mysqlmysql-error-1064
Владимир Говорухин
Владимир Говорухин

Reputation: 523

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

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?

Upvotes: 2

Views: 243

Answers (1)

GMB
GMB

Reputation: 222382

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

Upvotes: 1

Related Questions