CODEWITHSUNDEEP
mysqlmultiple-columns
DroidOS
DroidOS

Reputation: 8890

mySQL length of all columns

I have often SQL statements of the kind

SELECT LENGTH(col_name) FROM `table` WHERE *condition*

to establish the size of the contents of a specific column in a given row of a mySQL table. However, it is not clear to me that there is a single SQL statement that would fetch the sum of the content lengths of ALL the columns in a given row. I should add that all the columns in question are VARCHARS.

Yes, I know I could do this by fetching the entire row as

SELECT * FROM `table` WHERE *condition*

collapsing the resulting row contents into a string and getting the length of that string but I was wondering if there isn't a more efficient one liner to do the job. Any tips would be much appreciated.

Upvotes: 3

Views: 4557

Answers (3)

John Woo
John Woo

Reputation: 263713

Well, I prefer to use CHAR_LENGTH over LENGTH

SELECT CHAR_LENGTH(CONCAT(col1,col2,col3)) 
FROM tableName 
WHERE...

From the linked question

LENGTH() returns the length of the string measured in bytes.

CHAR_LENGTH() returns the length of the string measured in characters.

Upvotes: 5

vladknez
vladknez

Reputation: 41

Try this solution without going into column names:

SELECT SUM(character_maximum_length) length,
  table_name
  FROM information_schema.columns 
  WHERE table_name='your_table_name_here'
  AND table_schema=DATABASE()
  -- AND column_name IN ('choose', 'columns')
  GROUP BY table_name;

Upvotes: 2

Greg Oks
Greg Oks

Reputation: 2730

Have you tried:

SELECT char_length(col_name)+char_length(col_name2) FROM table WHERE condition

Upvotes: 0

Related Questions