CODEWITHSUNDEEP
mysql
Steve
Steve

Reputation: 1877

Determine max length allowed in a column in mysql

The structure of the table is like:

registrant_id   varchar(16)
registrant_name     varchar(128)

I want to run a query that displays all those entries which match the maximum PERMISSIBLE length, ie what I do currently do for the above:

SELECT *
FROM `tm_registrant`
WHERE length( `registrant_name` ) = 128

However, I dont want to hardcode 128 as I have many columns and want to run one query to see all the entries which have atleast one completely 'filled up' column.

Upvotes: 3

Views: 3139

Answers (2)

Mladen
Mladen

Reputation: 675

SELECT CHARACTER_MAXIMUM_LENGTH
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'your_table_name'
   AND table_schema = 'your_db_name'
   AND column_name = 'your_column_name'
 LIMIT 0 , 1

Upvotes: 6

Atef
Atef

Reputation: 73

you can use a combination left, right and len to extract the lenght of the varchar fields using this command in your SQL statment: SHOW FIELDS FROM tablename1 Where Field = 'registrant_name'

Or you can look in the NFORMATION_SCHEMA.COLUMNS http://dev.mysql.com/doc/refman/5.1/en/columns-table.html

Upvotes: 1

Related Questions