Geek asks and answers

How can I return all columns in a table that contains only data with four characters or less?

I am matching data from a MySQL table and have a table with more than 50 columns and more than 100,000 rows, and I need to match a column that has data that has 4 characters or less .

How can I get a list of all columns in a table that contain data containing only four characters or less? In my case, all columns are varchar (255).

+5
source share
4 answers
SELECT
  COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = <table>
AND CHAR_LENGTH(COLUMN_NAME) <= 4
+1
source

This will give you columns with a name of 4 or less characters:

SHOW COLUMNS FROM table WHERE CHAR_LENGTH(Field) < 5

sql table , Field , .

UPDATE:
4 , :

select * from table where CHAR_LENGTH(column_name) < 5
0

I think you need to create a dynamic query using the information from INFORMATION_SCHEMA.COLUMNS, and then execute this. Unverified, but something like:

SET @s = 'SELECT GROUP_CONCAT(X) AS FOUR_CHAR_COLS, ID FROM (' + 
        (SELECT GROUP_CONCAT('SELECT CASE WHEN CHAR_LENGTH(`' + COLUMN_NAME + 
        '`) <= 4 THEN ''' + COLUMN_NAME + ''' ELSE NULL END AS X, ID FROM MyTableName' 
          SEPARATOR ' UNION ALL ')
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MyTableName') + 
    ') XX GROUP BY ID';

PREPARE stmt FROM @s;
EXECUTE stmt;

This should contain a list of all columns in each row with 4 characters or less.

0
source

This can be done using a stored procedure.

  • Select all column names that are important from the table and are stored in the temporary table;
  • Iterating through a WHILE or REPEAT loop through them and executing the SELECT LENGTH (column_name) FROM table.
0
source

More articles:


All Articles