[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

SELECT LEN(' ') returns zero

I have to supply an output data feed where they want every column to have at least one blank character -- even if it is null.  I tried

SELECT ISNULL(aColumnName, '<BLANK>') but to check it out I added a LEN() function and it is returning zero.

NOTE: <BLANK> is just a space character.

I tried variations using the SPACE() function and the REPLICATE() function but it always returns a zero length from my select.  How can I get the query to return a blank for null columns?
  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
SELECT ISNULL(aColumnName, cast( ' ' as char(1)) )
LEN in SQL Server trims trailing blanks...

you need to use DATALENGTH( columnname) to obtain the proper length of a column always (it returns the
number of bytes used)

a space/blank is NOT the same as a NULL

a Null doesn't exist... signifies an unknown state... and has significance in aggregate functions....
a blank/space or even  zero length string is an actual data values...

jeffrAuthor Commented:
Thanks!  I didn't know that LEN() trims trailing blanks.  So it turns out that my query returned what I needed all along.
sorry len() ignores trailing blanks (rather than actually trimming them)

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now