Something wrong in Preprending string with spaces

Question

Based on the suggestion in this post Right pad a string with variable number of spaces

For testing purposes, I created a table and added the following rows

CREATE TABLE CharTest (
  ID int,
  CharField char(6) NULL
);

INSERT INTO CharTest
VALUES (1, '    90');

INSERT INTO CharTest
VALUES (1, '    89');

INSERT INTO CharTest
VALUES (1, NULL);

INSERT INTO CharTest
VALUES (1, '    91');

INSERT INTO CharTest
VALUES (1, '    90');

I then tried executing this query

DECLARE @lCharField char(6)
SET @lCharField = '90'

SELECT CharField FROM CharTest
where CharField = RIGHT(space(6) + @lCharField, 6)

it returned no rows.

I checked the output of the following queries

SELECT RIGHT(space(6) + @lCharField, 6) FROM CharTest
SELECT RIGHT('      ' + @lCharField, 6) FROM CharTest

and it doesn't seem to right-pad or pre-prend the spaces as expected.

Any help in pointing out what I am doing wrong is highly appreciated.

Answer 1

The issue is this code:

DECLARE @lCharField char(6);
SET @lCharField = '90';

You think the result is '90' but it is really '90 '. That throws everything off. Use:

DECLARE @lCharField varchar(6);
SET @lCharField = '90';

Here is a db<>fiddle.

Note that finicky spaces are one important reason why varchar() is used -- say -- 100 times more often than char(). The only really good use for char() is for columns that are of fixed width, such as ISO3 code for counties.