I am trying to undestand why the below query is returning extra space after '3'. This is a screenshot from on of the SQL quizes available online. I would expect 'C' to be the correct answer. Is there anything that causes the extra space or might it be an error in the task?

CodePudding user response：

The char type always stores data for all of the allocated space. Therefore a char(5), if it's not null, will always have 5 characters. If you store one character in the field the remaining four will be spaces.

Therefore the actual result will look like this:

King Kong (3    )

But in the context of HTML, multiple whitespace characters in sequence render by default as a single space, so you see this on the screen:

King Kong (3 )

To fix this to get the expected King Kong (3), you could use varchar(5) instead of char(5) or alternatively call rtrim() before the final concatenation.

CodePudding user response：

This is misleading because the answers are not displayed in such a way that whitespace is preserved, probably unintentionally. (Right-click the answer and use "inspect element" to see what it is actually supposed to be.)

The answer should have been King Kong (3 ) - that is, four spaces and not one - but by rendering this in HTML (without white-space: pre-wrap; or a similar CSS rule), the whitespace was collapsed to one space.

The reason for there being four spaces is due to casting the number to char(5), i.e. a five-character-long string. Since the number 3 will need only one character to be displayed, the remaining four characters in the string will be filled with spaces, so that the total length of 3 is still five characters as it was specified.