SPACE Function not working as expected

Beacon77 131 Reputation points
2022-09-28T14:59:42.68+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2022-09-28T15:59:06.03+00:00

    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


  2. Tom Phillips 17,716 Reputation points
    2022-09-28T20:31:20.803+00:00

    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.

    0 comments No comments

  3. CosmogHong-MSFT 23,641 Reputation points Microsoft Vendor
    2022-09-29T02:52:18.22+00:00

    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

    0 comments No comments