CODEWITHSUNDEEP
mysql
alkhader
alkhader

Reputation: 998

Append empty character to each entry in mysql

I have a table of countries which has a countryName column consisting country names, where each country name has different name length. I need to make all the names in the column to have the same Length. I have used UPDATE country SET countryName = Concat(name,' '); to add empty character to each entry, but how can I make all the names have the same length?

Thank you,

Upvotes: 0

Views: 445

Answers (2)

Samson
Samson

Reputation: 2821

You should use MySQL RPAD AND LPAD functions

UPDATE country SET countryName = RPAD(name,60,' ');

This way ' ' will be appended until size 60 is reached.

Upvotes: 1

Zane Bien
Zane Bien

Reputation: 23125

This will update the countryName field right padded with spaces to the length of the longest countryName in the table:

UPDATE country a
CROSS JOIN
(
    SELECT MAX(CHAR_LENGTH(countryName)) AS maxlen
    FROM country
) b
SET a.countryName = RPAD(a.countryName, b.maxlen, ' ')

Upvotes: 1

Related Questions