3

This is not as simple as it may appear. I have struggled for hours and googled to no avail.

I want to produce a single column that always has a length of 30 characters, so if the column only contains 2 characters, the other 28 characters should be empty spaces.

I am trying to get two different columns into a single one, as follows:

select FirstName + LastName as FullName

I want to get the following result:

DesiredResult

That means that the first row will have 7 visible characters (Michael), followed by 8 spaces, followed by 6 visible characters (Jordan), followed by 9 spaces. That makes a total of 30 characters. The second row will have 5 visible characters (Magic), followed by 10 spaces, followed by 7 visible characters (Johnson), followed by 8 spaces. The third row will have 5 visible characters (Larry), followed by 10 spaces, followed by 4 visible characters (Bird), followed by 11 characters.

The length of both columns will always vary, so I am looking for code that works regardless of the length. This means that it always adds the correct number of blank spaces after the first name. As you probably noticed, it is always 15 characters (either visible or empty) for the first name and another 15 characters (either visible or empty) for the last name.

Does anyone know how to do this? Thanks in advance!

Incompetent77
  • 71
  • 1
  • 7
  • What if the first name is exactly 15 characters, e.g. `MyQuiteLongName`? Do we then output `MyQuiteLongNameSurname`, or drop a char to ensure there's a space between? – underscore_d Jun 29 '20 at 13:54
  • I was just trying to make the example simpler. In my case it is actually 30 characters. It cannot be longer. If it is, the second part starts right after, without a space – Incompetent77 Jun 29 '20 at 13:58

1 Answers1

4

Use char():

select concat(convert(char(15), firstname), convert(char(15), lastname))

The char() type automatically pads strings with spaces, so no more work is necessary. This also has the nice feature that if the first or last names are too long, only the first 15 characters are used.

This is explicitly documented for len() (to conform with the standard, I think):

Returns the number of characters of the specified string expression, excluding trailing spaces.

Followed by this recommendation:

Note

To return the number of bytes used to represent an expression, use the DATALENGTH function.

Here is a db<>fiddle.

SQL Server discounts the spaces when measuring the length. So, in the length of the above, you will get 21 and 20. To see the length without removing the tailing spaces, use datalength().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Wow, what a short and sweet answer @GordonLinoff! I was planning to answer, but after seeing yours, I'm too embarrassed to answer lol. Upvote from me :-) – Attie Wagner Jun 29 '20 at 13:52
  • When I try it with your code, or on db fiddle, I get it right, but not with my sql server – Incompetent77 Jun 29 '20 at 13:59
  • What _do_ you get on your SQL Server? How are you viewing the results in SSMS? – underscore_d Jun 29 '20 at 13:59
  • Can it be that is is actually right but that the empty spaces are smaller than the used spaces? I mean, my results don't show the second row's last name starting at the same point as the first row's last name, but it could be that visible spaces are much larger than the empty ones. Otherwise, I don't know why it does not 'seem' to work – Incompetent77 Jun 29 '20 at 14:06
  • Actually, it does not seem to work just as expected. I would assume that I get a length of 60 from the following: – Incompetent77 Jun 29 '20 at 14:10
  • select len(concat(convert(char(30),left(FirstName,30)), convert(char(30),left(LastName,30)))) from (values ('TestingTestingTestingTesting','Testing'),('Testing', 'TestingTestingTestingTestingTesting')) v(FirstName, LastName) – Incompetent77 Jun 29 '20 at 14:10
  • But I get 37 and 60. The objective is to always get a length of 60 – Incompetent77 Jun 29 '20 at 14:10
  • @Incompetent77 . . . That is because you are using `len()` rather than `datalength()`. I've updated the answer. – Gordon Linoff Jun 29 '20 at 14:15
  • Ok! The end objective is to actually produce a prn file (after I extract the data via SSIS) that shows the first 30 characters and then the second set of 30 characters, and then more columns. I guess the datalength is the real length of the column that would be used then, right? – Incompetent77 Jun 29 '20 at 14:20
  • @Incompetent77 . . . Of course you should test it -- because something else might decide that the spaces are not necessary. But yes, `datalength()` is doing what you want in this case. – Gordon Linoff Jun 29 '20 at 14:41
  • I tested it by producing a txt file with the result. It looks perfect! The fact that it looked weird in SSMS threw me off, but it definitely works like a charm. Thanks a bunch Gordon! – Incompetent77 Jun 29 '20 at 14:46