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
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
18 Comments. Leave new
Good tip!
Carmelo Lisciotto
Yes very good tip
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 ????????????????
Jorge,
FYI, it works well
select space(5-3)+’a’
DECLARE @spc as int
SET @spc = 5
SELECT space(@spc)+’a’
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…
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.
You’re right.
Thank You for your patience.
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
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?
Thanks useful tip
Roshan from Sri Lanka
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
I want to insert/display 31 blank space, for that i use replicate/Space functions, but shows 40 spaces instead of 31 spaces
Post the exact code you have used
Good one..
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
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.
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
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