SQL - Difference between SPACE(2) + '|' + SPACE(2) and ‘ | ‘?

Question

Is there any difference between using SPACE(2) + '|' + SPACE(2) or just ' | '? I know the output will be the same but I'm not sure about other aspects... Actually I can't see the point in using SPACE function if there's no difference...

Thanks!

Answer 1

The first difference which comes to my mind is the amount of spaces. Let's say you'd like to write 20 spaces, a word and another 20 spaces:

'                    abcd                    '

How many spaces are there? Much more useful:

SPACE(20)+'abcd'+SPACE(20)

Isn't it?

There are many cases in which you edit somebody's code generating SQL queries. It's more than probable that you delete a space somewhere and not possible to debug it. There's no such problem if SPACE function is used.

Answer 2

I agree with others who have answered this that SPACE() is useful to indicate the number of spaces used or to add large numbers of spaces.

Using SPACE may also be simpler if you want to generate a fixed-width format file - you can use the data length as a parameter e.g.

SELECT 
  name + SPACE(20-LEN(name))
FROM
  SomeTableWithNames