David Sam David Sam - 4 months ago 19
SQL Question

mysql to append a number of stars based on the string length

I have a requirement that when a string length is less than 10, I need to append as many stars(*) at the end till length equals to 10.

ex:

ipString = "Stack"

opString = "Stack*****"


Is there any way to do it?

Answer

Use RPAD

SELECT RPAD('STACK',10,'*')

More accurately:

SET @str := 'Stack ';

SELECT 
IF(LENGTH(@str) < 10, RPAD(@str,10,'*') ,@str)

WORKING DEMO If the length of the string is less than 10

WORKING DEMO If the length of the string is greater than or equal to 10


Note on RPAD:

Description

MySQL RPAD() function pads strings from right. The actual string which is to be padded, length of the string returned after padding and string which is used for padding - all these are passed as arguments.

Syntax

RPAD(str, len, padstr)

Arguments

Name    Description
str     The actual string which is to be padded.
len     Length of the string returned after padding.
padstr  String which will be used for padding.
Comments