# Thread: Function like Len() that counts white space

1. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

## Unanswered: Function like Len() that counts white space

Good day to you, community friend!

Just a quick one - I swear I've seen a function before that was like Len() but it counted the white spaces too. I have a feeling it was something along the lines of "data_length" but I can't for the life of me find it!

Many thanks,
George

2. Registered User
Join Date
Nov 2005
Posts
122
Skip the underscore.

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
I knew it was something that simple!!
Cheers keffenils

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I appear to have slipped a cog or two here. Can someone explain what georgev meant based on:
Code:
```DECLARE @c		VARCHAR(50)

SELECT @c = 'This is a test'

SELECT Len(@c), DataLength(@c), @c AS '12345678901234567890'```
-PatP

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Code:
```DECLARE @c VARCHAR(50)

SELECT @c ='This is a test                             '

SELECTLen(@c),DataLength(@c), @c AS'12345678901234567890'
```

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Ah ha! I keep thinking like SQL Server, so trailing whitespace isn't really there in my mind... This was actually a user-required concession to make CHAR() columns function the way that microcomputer programmers expected them to, long before Microsoft inherited the product.

-PatP

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
The trailing spaces are maintained in SQL Server CHAR columns. It is the LEN function that ignores them when returning a value.

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by blindman
The trailing spaces are maintained in SQL Server CHAR columns. It is the LEN function that ignores them when returning a value.
That's what I thought I said.

Somewhere prior to 4.0, spaces were left "as is" by functions, including trailing spaces. By the time that 4.21 was released, all of the string functions were supposed to trim trailing spaces before processing, and convert any string results back into the CHAR() type if that is what the function had originally been passed.

-PatP

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•