[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

SQL Server Right Padding spaces

I need to right pad a sql Server field with spaces.  I have tried:
 Select Field + replicate(char(32), 10)
 Select Field + space(10)
Select Cast(Field AS CHAR) + replicate(char(32), 10)
Etc
The field is NVARCHAR(50)

I can right pad with any character BUT space.

I tried ANSI_PADDING both OFF and ON
0
tim44202
Asked:
tim44202
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
How about...
Declare @str nvarchar(50) = '123456'
SELECT @str + SPACE(50 - LEN(@str)) + 'looky here, a 50 character string'

Open in new window

0
 
LowfatspreadCommented:
sorry how is it failling?

you do know that len(columnname) ignores trailing spaces...?

use datalength(columnname)  for the true length .... (it gives the true  number of bytes consumed...)
0
 
tim44202Author Commented:
Great job of seeing through the fog!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So ... what was the answer?
0
 
tim44202Author Commented:
The answer is that there was no problem,  The use of LEN(columnName) made it look as if there were a problem.The recommended use of datalength(ColumnName) gives the real length in bytes.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now