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 (http://blog.SQLAuthority.com), BOL
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