SELECT LEN(' ') returns zero

Posted on 2006-06-07
Last Modified: 2008-02-26
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?
Question by:jeffr
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    please try this:
    SELECT ISNULL(aColumnName, cast( ' ' as char(1)) )
    LVL 50

    Accepted Solution

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


    Author Comment

    Thanks!  I didn't know that LEN() trims trailing blanks.  So it turns out that my query returned what I needed all along.
    LVL 50

    Expert Comment

    sorry len() ignores trailing blanks (rather than actually trimming them)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now