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?

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

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

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL

,
Previous Post
SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup
Next Post
SQL SERVER – 2005 – SSMS – View/Send Query Results to Text/Grid/Files

Related Posts

18 Comments. Leave new

  • Carmelo Lisciotto
    July 19, 2007 10:40 am

    Good tip!

    Carmelo Lisciotto

    Reply
  • Yes very good tip

    Reply
  • its sad that this doesnt work:

    space(5-3)

    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 ????????????????

    Reply
  • Jorge,

    FYI, it works well

    select space(5-3)+’a’

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

    Reply
  • 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…

    Reply
  • Jorge,

    All your examples work just fine. Try these examples:

    SELECT LEN(SPACE(5-3));
    SELECT LEN(SPACE(5-3) + ‘X’);
    SELECT DATALENGTH(SPACE(5-3));
    SELECT DATALENGTH(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.

    Reply
  • You’re right.

    Thank You for your patience.

    Reply
  • 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

    Sandeep

    Reply
  • 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?

    Reply
  • Thanks useful tip

    Roshan from Sri Lanka

    Reply
  • 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

    Reply
  • I want to insert/display 31 blank space, for that i use replicate/Space functions, but shows 40 spaces instead of 31 spaces

    Reply
  • Deiveegaraja. A
    March 6, 2012 6:04 pm

    Good one..

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • hi i want to combine two columns for show it in dropdown list but problem is the alignment after combine how can i solve it…

    for further description

    Reply

Leave a Reply

Menu