SPACE Function not working as expected

Question

Hi All,
I am using SPACE() function . I am adding "employeeid+space(3)" , but when i check the length using the LEN function in the outer query, it is still showing 11 instead of 14. The source data type for the employee id is VARCHAR(14) and the incoming data can be 11(min) to (14) max, so formatting purposes I have to use this. Please advice.
Thanks

Answer 1

LEN does not count trailing spaces. If you want to know the length including trailing spaces you need to use DATALENGTH. DATALENGTH returns the number of bytes including any trailing spaces. But since VARCHAR is one byte per character, it will return 14 if you have 11 characters in employeeid and add three spaces.

However, you don't want to just add three spaces, since if employeeid is 14 characters long and you add three spaces,, it's now 17 characters, Depending on how you are using the result, that may or may not be a problem. I would recommend using

LEFT(employeeid + SPACE(3), 14)

as that will add 3 spaces and then truncate the result to 14 characters if adding the 3 spaces made the result more than 14 characters.

Tom

Answer 2

See:
https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-ver16#remarks

LEN excludes trailing spaces. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string.

Answer 3

Hi @Beacon77
As experts answered, you need to use DATALENGTH to avoid trimming the string.

The source data type for the employee id is VARCHAR(14) and the incoming data can be 11(min) to (14) max, so formatting purposes I have to use this.

Maybe you could try changing VARCHAR(14) to CHAR(14).

Best regards
Li Hong