MySql - select columns which has data length less than N length

Question

I have a table in mysql contains almost 150 columns. I want to find out those column name which has no more than 3 character data. I am not finding the column whose data type length is less than 3 character.

Example

+-----------------+-----------------+-----------------+
| Col1 (varchar10)| Col2 (varchar10)| col3 (varchar11)|
+-----------------+-----------------+-----------------+
|              11 |            1212 |            1212 |
|               1 |             122 |              12 |
|              15 |             123 |          134444 |
+-----------------+-----------------+-----------------+

The result should be like

+--------------+
| Column Names |
+--------------+
| Col1         |
+--------------+
| Col3         |
+--------------+.

So for I have come across this SQL to print column names but the length is actual column data type length. I want Data length.

SELECT column_name, 
       character_maximum_length 
FROM   information_schema.columns 
WHERE  table_schema = 'tvs' and table_name = 'xyz'

Note: This example show varchar(10) data type but in actual I have numeric as well var char columns.

Use `MAX(CHAR_LENGTH(column_name))` to get max character length per column, and then consider only where the character length <= 3 — Madhur Bhaiya, Aug 23 '19 at 11:52
I have more than 150 columns. I am looking for dynamic solution where I do not have to give column name explicitly. otherwise It took me to find all column names. Please I want to print column names. Not the Rows. — Mubasher, Aug 23 '19 at 11:54
You would want to build a stored procedure that iterates over information_schema for all of the column names SELECT COLUMN_NAME, FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name'. Hopefully someone comes across with a good example but this should give you a start of how to get the column names. — GenericDisplayName, Aug 23 '19 at 12:09
@Mubasher to the best of my knowledge you will need to create a stored procedure to do this. Look at the accepted answer for this question https://stackoverflow.com/questions/12718596/mysql-loop-through-tables?lq=1 — GenericDisplayName, Aug 23 '19 at 12:23

Cid · Accepted Answer · 2019-08-23T12:44:17.333

You can use dynamic SQL to build your query. There is some steps.

First, you might need to get the field names of your table :

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = 'yourdatabasename' 
    AND `TABLE_NAME` = 'yourtablename'
    AND `DATA_TYPE` = 'varchar'; -- This will get you only the varchar types fields

As stated @Madhur Bhaiya in comments, you can use MAX(CHAR_LENGTH(column_name)) to get the max length of a varchar in a field.

For a single field, this can use used such as

SELECT CASE WHEN MAX(CHAR_LENGTH(col1)) <= 3 THEN 'col1' END AS colName FROM yourdatabasename.yourtablename

This will display 'col1' if MAX(CHAR_LENGTH(col1)) <= 3

Now you can use the first query to build another MySQL query :

SELECT 'SELECT * FROM ('
UNION
SELECT CONCAT('SELECT CASE WHEN MAX(CHAR_LENGTH(', `COLUMN_NAME`, ')) <= 3 THEN \'', `COLUMN_NAME`, '\' END AS colName FROM yourdatabasename.yourtablename\nUNION')
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = 'yourdatabasename' 
    AND `TABLE_NAME` = 'yourtablename'
    AND `DATA_TYPE` = 'varchar'
UNION
SELECT 'SELECT NULL) cols WHERE colName IS NOT NULL';

This will give you an output such as :

SELECT * FROM (
SELECT CASE WHEN MAX(CHAR_LENGTH(col1)) <= 3 THEN 'col1' END AS colName FROM yourdatabasename.yourtablename
UNION
SELECT CASE WHEN MAX(CHAR_LENGTH(col2)) <= 3 THEN 'col2' END AS colName FROM yourdatabasename.yourtablename
UNION
SELECT CASE WHEN MAX(CHAR_LENGTH(col3)) <= 3 THEN 'col3' END AS colName FROM yourdatabasename.yourtablename
UNION
SELECT NULL) cols WHERE colName IS NOT NULL

You can now copy/paste this result and use it as query.

let me try. I think this is the closest one. Thanks. – Mubasher Aug 23 '19 at 12:32 — Mubasher, Aug 23 '19 at 12:32

MySql - select columns which has data length less than N length

1 Answers1