How to hard code spaces at end of result?

Question

I need to add spaces to the end of a column I have called NPI (nvarchar(20)). NPI is always 10 digits, but the requirements for the report want the first 10 digits to be the NPI followed by 10 spaces (text file formatting issues I assume). I have tried the following:

cast([NPI] as nvarchar(20)) + '          '
cast([NPI] as nvarchar(20)) + Space(10)

However, the result set does not change. It just shows the 10 digit NPI and spaces aren't included.

Answer 1

It sounds like you are actually using SQL Server instead of MySQL. VARCHAR() is for variable length strings and it will trim end whitespace. Cast instead to char/nchar for the desired output. It won't look like it in SSMS, so check datalength to confirm nchar(20) = 20 bytes * 2 for unicode.

SELECT CAST([NPI] AS NCHAR(20)) AS formattedNPI, 
DATALENGTH(CAST([NPI] AS NCHAR(20))) AS confirmation
FROM your_table