0

I'm generating a recordset from a SQL Server (2008 R2) stored procedure that fills a ComboBox in excel VBA.

If I run a query to get the length of each value returned from the table it looks like so:

Length Example

SELECT [outcome], LEN([outcome]) AS [Length] FROM [dbo].[crl_outcome] GROUP BY [outcome]

When executing the stored procedure, I assign the recordset to a variant array, for example:

varList = oRS.GetRows()
MsgBox Len(varList(1, 0))

The MsgBox returns the field shown in the SQL statement above that's 21 characters long in the table as being 128 characters in length with spaces making up the remaining 107 characters.

As you can see, there's no formatting being done on the value but the field in the table is specified as nchar(128).

Does anyone know why the each result is having spaces appended the end of them?

Gareth
  • 5,140
  • 5
  • 42
  • 73
  • 4
    I believe that's the nature of `nchar`. Try `nvarchar` instead, as it doesn't pad the data with spaces. I think. – Doug Glancy Dec 21 '14 at 23:37
  • I agree with Doug that you need to use nvarchar to avoid padding. If you use the DATALENGTH function instead of LEN (which removes trailing spaces) in your query, I think you will find the length returned is 256, which is the actual data length in bytes as Unicode uses 2 bytes per character. – Dan Guzman Dec 22 '14 at 01:01
  • Yep agreed. Char and nchar pad the strings – Grantly Dec 22 '14 at 01:06
  • @DougGlancy Thanks for your help, this did the trick. Stick it into an answer and happy to accept - might want to note in the answer that the existing records will need trimming to remove the padding. – Gareth Dec 22 '14 at 11:15

1 Answers1

1

I believe that's the nature of nchar. Try nvarchar instead, as it doesn't pad the data with spaces. Here's another See this previous SO discussion for more details.

As you mentioned you can remove trailing spaces from existing strings with a TRIM function.

Community
  • 1
  • 1
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115