0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
José Paulo R. de Lima
  • 32
  • 5
  • 2
    If trailing whitespace is significant then it might be wise to use `varbinary` instead of `varchar` - assuming you don't need to perform string operations in SQL. – Dai Jul 07 '22 at 01:39
  • 4
    BTW, `LEN()` always ignores trailing whitespace: https://stackoverflow.com/questions/2025585/len-function-not-including-trailing-spaces-in-sql-server – Dai Jul 07 '22 at 01:39
  • 2
    According to the documentation on [len()](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-ver16) `Returns the number of characters of the specified string expression, excluding trailing spaces.` – Squirrel Jul 07 '22 at 01:39
  • 1
    `DECLARE @Test varchar(32); SELECT @Test = 'ABC' + SPACE(10); SELECT @Test + ':';` works i.e. gives a string 14 chars long which appears to be what you want. – Dale K Jul 07 '22 at 01:41
  • In this particular situation, I can't change field datatypes. – José Paulo R. de Lima Jul 07 '22 at 01:42
  • `REPLICATE(' ', 5)` does returns you 5 spaces. If can't use `len()` to determine the length as it ignore the trailing spaces – Squirrel Jul 07 '22 at 01:45
  • 1
    @JoséPauloR.deLima you don't need to add the colon to make the spaces stay there, my code illustrates that the spaces **are** there since `LEN` doesn't tell you. But they are there whether or not you add the colon - so don't add the colon. – Dale K Jul 07 '22 at 01:53
  • @DaleK thanks for your answer. In fact, I'm trying to join a CHAR(35) with a VARCHAR(35). In order to match the join, I have to add whitespace. I cant' use TRIM on CHAR(35) because it's on AS400 server and it makes the query extremely slow. – José Paulo R. de Lima Jul 07 '22 at 01:59
  • 1
    @JoséPauloR.deLima so whats not working about what you are trying to do? Create a [mre] that demonstrates your actual issue. – Dale K Jul 07 '22 at 02:00
  • [How SQL Server Compares Strings with Trailing Spaces](https://support.microsoft.com/en-us/topic/inf-how-sql-server-compares-strings-with-trailing-spaces-b62b1a2d-27d3-4260-216d-a605719003b0) – Squirrel Jul 07 '22 at 02:14
  • @DaleK I added an example. It's hard to emulate because it uses to differente databases. – José Paulo R. de Lima Jul 07 '22 at 02:17
  • 1
    @JoséPauloR.deLima its pretty important you include those sorts of details right from the start... how are you linking your databases? – Dale K Jul 07 '22 at 02:18
  • @DaleK sorry for not posting from start. I'm using IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider on SQL Server Linked Servers. – José Paulo R. de Lima Jul 07 '22 at 02:26
  • 3
    I doubt this has anything to do with the trailing space, as SQL Server will pad the `varchar` to match the `char`. Probably a different collation or weird characters. We need a [mcve] – Charlieface Jul 07 '22 at 03:01
  • @Charlieface if both tables were on SQL Server, it would work. It pads the ````char```` to match the ````varchar````. But as the ````char```` field is on DB2, it does not pad. – José Paulo R. de Lima Jul 07 '22 at 03:21

3 Answers3

1

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))
CaptainPyscho
  • 338
  • 1
  • 7
-2
'ABC' + REPLICATE(' ',10)

works

Dale K
  • 25,246
  • 15
  • 42
  • 71
JohnneyDarkness
  • 7
  • 3
  • Trying on SQL Server 15.0.something gives my your result, SPACE(n) is always one space. – JohnneyDarkness Jul 07 '22 at 01:36
  • If you use ````LEN('ABC' + REPLICATE(' ', 10))```` it returns ````3````. If you use ````DATALENGHT('ABC' + REPLICATE(' ', 10))```` it returns ````13````. But I need to use ````'ABC' + REPLICATE(' ', 10)```` to join with a field. Then, it removes whitespace – José Paulo R. de Lima Jul 07 '22 at 01:37
  • 1
    `SPACE(n) is always one space` that is not true. Not sure how do you come to that conclusion – Squirrel Jul 07 '22 at 01:43
  • Right you are. SELECT LEN('ABC' + REPLICATE(' ',10) + '.') shows a length of 14. I don't like what the LEN function is doing. – JohnneyDarkness Jul 07 '22 at 01:51
  • @Squirrel - you are correct I was making a false observation assuming the LEN() function worked like I thought it did. – JohnneyDarkness Jul 07 '22 at 01:56
-2
SPACE(INTEGER) is working fine  
select LEN('abc'+SPACE(10))    `3`

LEN() function excludes trailing blanks/whitespaces

select DATALENGTH('abc'+SPACE(10))   `13`

using DATALENGTH() give you 13, DATALENGTH() includes trailing blanks/whitespaces

zeeshan12396
  • 382
  • 1
  • 8