[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server Right Padding spaces

Posted on 2015-01-20
5
Medium Priority
?
452 Views
Last Modified: 2015-01-20
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
Comment
Question by:tim44202
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40560507
How about...
Declare @str nvarchar(50) = '123456'
SELECT @str + SPACE(50 - LEN(@str)) + 'looky here, a 50 character string'

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 40560569
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
 

Author Closing Comment

by:tim44202
ID: 40560839
Great job of seeing through the fog!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40561079
So ... what was the answer?
0
 

Author Comment

by:tim44202
ID: 40561121
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question