SQL SERVER – SPACE Function Example

A month ago, I wrote about SQL SERVER – TRIM() Function – UDF TRIM() . I was asked in comment if SQL Server has space function?

will generate 100 space characters. The use of SPACE() function is demonstrated in BOL very fine.

Example from BOL:
USE AdventureWorks;
SELECT RTRIM(LastName) + ',' + SPACE(2) + LTRIM(FirstName)
FROM Person.Contact
ORDER BY LastName, FirstName;

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

18 thoughts on “SQL SERVER – SPACE Function Example

  1. its sad that this doesnt work:


    this returns a string with no spaces :(

    SELECT LEN(space(5-3)) -> 0

    neither this works:

    DECLARE @spc as int
    SET @spc = 5
    SELECT space(@spc)

    You want SQL Server to be taken seriously ????????????????


  2. What a wonderfull workaroud that is… why other DB makers didnt do it that way ?????

    Did you know that in MS Access, space(5-3) works as expected ?

    Still… This is a bug (a naive one), no matter what ‘a’, ‘b’ or ‘c’ is appended to the end, appending a space, doesnt work again…


  3. Jorge,

    All your examples work just fine. Try these examples:

    SELECT LEN(SPACE(5-3) + ‘X’);

    If you lookup the LEN function you will see that it states these two things:

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

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

    This is obviously a case of RTM.


  4. Hello
    Please any one tell me
    Can i change face,size or color of my data using such inbuilt functions ?
    and how?

    Thanx in advance



  5. Great tip, as always.

    Do you think there is any value in using the space function instead of a ” if you want to return an empty string?


  6. After using len() function the length of code field show two different value. This data is import from excel to sql server.
    How to find the character which not shown in code but count in len() function.

    Code Length
    P42400020 9
    P42400020  10


  7. select ‘sql’ +space(6-5)+ ‘2008’ output sql 2008
    select ‘sql’ +space(6-6)+ ‘2008’ output sql2008
    select ‘sql’ +space(6-7)+ ‘2008’ output null
    select ‘sql’ +space(6-6)+ 2008 output Conversion failed while converting the varchar value ‘sql’ to data type int
    select 1 + space (6-4) + 2 output 3
    select 1 + space (6-6) + 2 output NULL


  8. how would you write a SQL script to see what id numbers are same, except some have just one space in front? For ex. Table 1 has a column with id 1234 and then a blank space 1234. I want to see how many of those there are in the database.


  9. I have a problem error with (+) string,I don’t know to solutions do it.

    DELETE FROM + SPACE (1) + @TblName + SPACE(1) WHERE + SPACE(1) + @FieldName =@Name


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s