How to get the most characters stored in a column of a table?

Question

How to get the most characters stored in a column of a table?

Navigation

#1 by (2 votes)

0

I have a table products where the column code is a TINYTEXT .
When trying to create a foreign key ( FOREING KEY ) I get this error:

# 1170 - Column BLOB column 'code' used in key specification without key size

Since you can not create foreign keys using fields tinytext ( among others ), I decided to convert the column code to VARCHAR , but, to correctly define the length of VARCHAR I need to know:

How many characters does the longest code saved in the table have?

mysql sql

asked by Marcos 16.02.2018 в 17:06

source

1 answer

Problems with Click and Keydown function Save a table from a bd in a datatable and scroll through your information while taking your data

score 2 · Answer 1

I was able to get the result using CHAR_LENGTH .

Returns the length of the string str , measured in characters.

Solution:

SELECT CHAR_LENGTH(code) AS codeLength
FROM products
ORDER BY codeLength DESC
LIMIT 1

Explanation:

With SELECT CHAR_LENGTH(code) AS codeLength I get the number of characters that the code of each product has.

By adding ORDER BY codeLength DESC , I get the products ordered by their length of characters from largest to smallest, that is, those that have the code with more characters first .

And finally with LIMIT 1 , I get only the first one.