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.

Can you use: w3schools.com/sql/func_sqlserver_space.asp SPACE adds them — Brad
– Brad, Commented Mar 18, 2021 at 12:51

Gordon Linoff · Accepted Answer · 2021-03-18 12:45:17Z

3

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.

answered Mar 18, 2021 at 12:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Something wrong in Preprending string with spaces

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related