SQL Server removing necessary whitespaces-CodePudding

I'm trying to add whitespaces to a column. However, the SPACE function is not working.

Example:

SELECT LEN(('ABC'   SPACE(10)))

Returns 3 instead 13.

This behavior also happens with the REPLICATE function.

Example:

REPLICATE(' ', 5)

Returns ''

What I need:

REPLICATE(' ', 5)

Returns ' '

Editing:

This suggestion works, but it needs do add : to the string.

DECLARE @Test varchar(32); 

SELECT @Test = 'ABC'   SPACE(10); 
SELECT @Test   ':';

Returns ABC :

I need ABC

--This table is in SQL Server
CREATE TABLE Iten
(
    Code varchar(35)
);

--This table is on DB2
CREATE TABLE Product
(
    code char(35),
    description varchar(100)
)

INSERT INTO Iten VALUES ('ABC');
INSERT INTO Product VALUES ('ABC', 'My Test')

SELECT 
    Iten.Code, Product.description, 
    DATALENGTH(Iten.Code), 
    DATALENGTH(Product.code)
FROM
    Iten 
INNER JOIN 
    IBMServerD.DatabaseD.LDAT.Product AS Product ON Iten.Code = Product.code

This query returns no rows.

Because that, I need to fill spaces on the right to Iten.Code, but it does not respect it.

If both tables were in SQL Server, it would work fine.

CodePudding user response：

Try using trim functions

SELECT Iten.Code, Product.description, DataLength(Iten.Code), 
       DataLength(Product.code)
  from Iten INNER JOIN IBMServerD.DatabaseD.LDAT.Product as Product
    ON ltrim(rtrim(Iten.Code)) = ltrim(rtrim(Product.code))

CodePudding user response：

declare @name varchar(32)
set @name='abc'
select left(@name replicate(' ',10),len(@name) 10)

replicate the number of spaces you want (10), then extract the number of character from string ,from left i.e., number of space length of string (len(@name) 10)
use datalength() for length of string

CodePudding user response：

'ABC'   REPLICATE(' ',10)

works