CODEWITHSUNDEEP
sqlsql-servert-sqlspaceshardcode
anon123
anon123

Reputation: 57

How to hard code spaces at end of result?

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.

Upvotes: 0

Views: 368

Answers (3)

Joakim Danielson
Joakim Danielson

Reputation: 52108

Add the space inside the cast

cast([NPI] + '         ' as nchar(20))

You're right @dfundako, I was fooled by my editor.

This works but I am using MariaDb (MySql) so it's maybe not relevant now.

select concat([NPI] , '         ')

Upvotes: 0

dfundako
dfundako

Reputation: 8324

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

Upvotes: 2

Keith Aymar
Keith Aymar

Reputation: 924

This seems to work. The '*' is added to show that spaces are present..

print cast([NPI] as nchar(20)) + '*'

Here are a couple of other cheesy ways to add padding...

print substring([NPI] + '                    ',1,20) + '*'

print [NPI] + space(20 - len([NPI])) + '*'

Upvotes: 0

Related Questions