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.

I am using MySQL. I tried to change to nchar, no luck with output - no change. — Gregory Brauninger, Mar 16 '18 at 14:39
It seems like it should work, it just won't display in result set. — Gregory Brauninger, Mar 16 '18 at 14:40
@GregoryBrauninger Please check the data length using Length() to confirm how many bytes nchar vs nvarchar take. — dfundako, Mar 16 '18 at 14:44
cast([NPI] as nchar(20)) appears to be working. I just don't think SSMS displays trailing zeroes in result set. However, when I copy a value from the result set and paste it into a query, it has 10 spaces behind it. — Gregory Brauninger, Mar 16 '18 at 14:45
The results you are getting may have the spaces, but the query tool you are using might supress/hide them. Try exporting results to a csv and check for spaces there. Err...incidentally saying MySQL but using SSMS is a weird conflict. — Twelfth, Mar 16 '18 at 14:49
I apologize, I am using SSMS. dfundako, it appears your answer is correct. It just doesn't show in result set. — Gregory Brauninger, Mar 16 '18 at 14:50
Also, it won't really show in the result window. If you want piece of mind, check the DATALENGTH() of the value after you cast it to nchar(20). All values should come out to 40. — dfundako, Mar 16 '18 at 14:53
thats why i put an '*' at the end of my example, so that you will see the spaces... — Keith Aymar, Mar 16 '18 at 14:58

dfundako · Accepted Answer · 2018-03-16T14:57:57.783

2

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

edited Mar 16 '18 at 14:57

answered Mar 16 '18 at 14:55

dfundako

8,022
3
18
34

Just an echo, SSMS is likely formatting your output, so the results in the SSMS results pane may not appear to have the spaces. – Twelfth Mar 16 '18 at 14:57
Thank you!!! For anyone else, this may not show in result set, but if you export to text or csv, or copy and paste into query, the spaces will be there. – Gregory Brauninger Mar 16 '18 at 14:57
Also, @GregoryBrauninger if you are working with NPI's and need to verify the NPI is valid, go look up Luhn algorithm. – dfundako Mar 16 '18 at 14:58

Joakim Danielson · Answer 2 · 2018-03-16T15:04:18.300

0

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] , '         ')

edited Mar 16 '18 at 15:04

answered Mar 16 '18 at 14:49

Joakim Danielson

43,251
5
22
52

1

This is unnecessary. Casting to char/nchar will pad the end with blanks. – dfundako Mar 16 '18 at 14:52
Doesn't show in result set, but I am sure they are there, just being suprresed as Twelfth mentioned. – Gregory Brauninger Mar 16 '18 at 14:53

Keith Aymar · Answer 3 · 2018-03-16T14:57:08.847

0

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])) + '*'

edited Mar 16 '18 at 14:57

answered Mar 16 '18 at 14:50

Keith Aymar

876
7
10

How to hard code spaces at end of result?

3 Answers3