LEN()

Question

LEN()

April 2, 2013 at 2:18 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Primo Dang SSCrazy Points: 2643 More actions · Answer 1

L' Eomot Inversé (3/29/2013)
Primo Dang (3/29/2013)
I'm glad so many people enjoyed it, thank you! =]
I decided to submit this question when I saw this question from 2010[/url], because some people mentioned there should be an option involving leading spaces.
That seems to be the wrong link - today's question, not a 2010 one.

Are you sure? The name is the same and the question is very similar, but I tried clicking the link and it was the old one. Here's the URL: http://www.sqlservercentral.com/questions/LEN/69562/[/url]

Edit: included actual link on the URL text.

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Primo Dang (3/29/2013)
L' Eomot Inversé (3/29/2013)
Primo Dang (3/29/2013)
I'm glad so many people enjoyed it, thank you! =]
I decided to submit this question when I saw this question from 2010[/url], because some people mentioned there should be an option involving leading spaces.
That seems to be the wrong link - today's question, not a 2010 one.
Are you sure? The name is the same and the question is very similar, but I tried clicking the link and it was the old one. Here's the URL: http://www.sqlservercentral.com/questions/LEN/69562/[/url]
Edit: included actual link on the URL text.

No, I was wrong. Careless of me. :blush:

I mistakenly looked at the question list on the page before checking the question text, but apparently a link to the old question delivers a page with the old question above todays list of recent questions. Positively weird :crazy:!

Tom

Primo Dang SSCrazy Points: 2643 More actions · Answer 3

Yeah, the question list defaults to the latest questions, no matter to which question you specifically link.

I actually have a lot of trouble answering old questions because of that. Every day, I have to change the questions per page to 100 and navigate back page by (agonizing, to borrow from Jeff Moden) page to the ones I haven't answered. =P

Bangla Hall of Fame Points: 3137 More actions · Answer 4

"...excluding trailing spaces...." is the key to the answer...Nice question....

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 5

I'm not aware of that LEN() willl ignore trailing spaces. Thanks for making me to learn..

If somebody explains the difference b/w Datalength() & Len() in this thread will be useful.

--
Dineshbabu
Desire to learn new things..

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

Dineshbabu (4/1/2013)
If somebody explains the difference b/w Datalength() & Len() in this thread will be useful.

Datalength returns the amount of bytes that would be used on disk for storing the data in uncompressed form (excluding the two bytes overhead for varying length data). For varchar, that is the actual number of characters, including trailing spaces. For nvarchar, it is twice that, because each unicode character takes up two bytes.

For char, datalength always returns the declared length, because char values are always padded up to the specified maximum length. And for nchar, that is agaim multiplied by two.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Miles Neale SSChampion Points: 13147 More actions · Answer 7

Nice question.

Thanks Hugo for your work as well.

M.

Not all gray hairs are Dinosaurs!

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 8

Thanks Hugo,

I have one more doubt, will ANSI_PADDING set option affects the datastorage?

--
Dineshbabu
Desire to learn new things..

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

Dineshbabu (4/1/2013)
Thanks Hugo,
I have one more doubt, will ANSI_PADDING set option affects the datastorage?

No and yes.

No, because the working of LEN and DATA_LENGTH are not affected by this option, they still work exactly the same.

Yes, because as a result of indirect effects, the results can still be different.

Here's a repro:

SET ANSI_PADDING OFF;

CREATE TABLE dbo.Test

(c char(10), nc nchar(10), vc varchar(10), nvc nvarchar(10));

INSERT INTO dbo.Test(c, nc, vc, nvc)

VALUES ('abc ', N'abc ', 'abc ', N'abc ');

SELECT LEN(c), LEN(nc), LEN(vc), LEN(nvc)

FROM dbo.Test;

SELECT DATALENGTH(c), DATALENGTH(nc), DATALENGTH(vc), DATALENGTH(nvc)

FROM dbo.Test;

go

DROP TABLE dbo.Test;

go

Run this twice, with the ANSI_PADDING option ON and OFF. As you see, the DATALENGTH of the vc column is affected by the option. But not because the function behaviour is changed by the option - the change is in the INSERT statement. Here, the trailing spaces will be removed from the varchar column when the option is off. Since the trailing spaces are removed, only the letters abc are stored, and DATALENGTH correctly reports three bytes used.

Some important warnings:

1. ANSI_PADDING affects varchar columns, but not nvarchar columns. And varchar(max) columns are also excluded.

2. ANSI_PADDING only affects data that is stored in a table. When assigning values to a variable, this option has no effect.

3. ANSI_PADDING is a deprecated option. Both the option and the ANSI_PADDING OFF behaviour will be removed in a future version of SQL Server. Don't use this option in new code, and replace it if it's still being used in existing code.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

Hugo Kornelis (4/2/2013)
Dineshbabu (4/1/2013)
Thanks Hugo,
I have one more doubt, will ANSI_PADDING set option affects the datastorage?
No and yes.
No, because the working of LEN and DATA_LENGTH are not affected by this option, they still work exactly the same.
Yes, because as a result of indirect effects, the results can still be different.
Here's a repro:
SET ANSI_PADDING OFF;
CREATE TABLE dbo.Test
(c char(10), nc nchar(10), vc varchar(10), nvc nvarchar(10));
INSERT INTO dbo.Test(c, nc, vc, nvc)
VALUES ('abc ', N'abc ', 'abc ', N'abc ');
SELECT LEN(c), LEN(nc), LEN(vc), LEN(nvc)
FROM dbo.Test;
SELECT DATALENGTH(c), DATALENGTH(nc), DATALENGTH(vc), DATALENGTH(nvc)
FROM dbo.Test;
go
DROP TABLE dbo.Test;
go
Run this twice, with the ANSI_PADDING option ON and OFF. As you see, the DATALENGTH of the vc column is affected by the option. But not because the function behaviour is changed by the option - the change is in the INSERT statement. Here, the trailing spaces will be removed from the varchar column when the option is off. Since the trailing spaces are removed, only the letters abc are stored, and DATALENGTH correctly reports three bytes used.
Some important warnings:
1. ANSI_PADDING affects varchar columns, but not nvarchar columns. And varchar(max) columns are also excluded.
2. ANSI_PADDING only affects data that is stored in a table. When assigning values to a variable, this option has no effect.
3. ANSI_PADDING is a deprecated option. Both the option and the ANSI_PADDING OFF behaviour will be removed in a future version of SQL Server. Don't use this option in new code, and replace it if it's still being used in existing code.

gr88 Explanation Hugo 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Bangla Hall of Fame Points: 3137 More actions · Answer 11

Bangla

Hall of Fame

Points: 3137

April 2, 2013 at 2:25 am

#1602697

Thanks Hugo, learn something..

manik_anu SSCrazy Points: 2377 More actions · Answer 12

Hugo Kornelis (4/1/2013)
Dineshbabu (4/1/2013)
If somebody explains the difference b/w Datalength() & Len() in this thread will be useful.
Datalength returns the amount of bytes that would be used on disk for storing the data in uncompressed form (excluding the two bytes overhead for varying length data). For varchar, that is the actual number of characters, including trailing spaces. For nvarchar, it is twice that, because each unicode character takes up two bytes.
For char, datalength always returns the declared length, because char values are always padded up to the specified maximum length. And for nchar, that is agaim multiplied by two.

nice one... thanks hugo....

****manik****

Manik
You cannot get to the top by sitting on your bottom.

manik_anu SSCrazy Points: 2377 More actions · Answer 13

Hugo Kornelis (4/2/2013)
Dineshbabu (4/1/2013)
Thanks Hugo,
I have one more doubt, will ANSI_PADDING set option affects the datastorage?
No and yes.
No, because the working of LEN and DATA_LENGTH are not affected by this option, they still work exactly the same.
Yes, because as a result of indirect effects, the results can still be different.
Here's a repro:
SET ANSI_PADDING OFF;
CREATE TABLE dbo.Test
(c char(10), nc nchar(10), vc varchar(10), nvc nvarchar(10));
INSERT INTO dbo.Test(c, nc, vc, nvc)
VALUES ('abc ', N'abc ', 'abc ', N'abc ');
SELECT LEN(c), LEN(nc), LEN(vc), LEN(nvc)
FROM dbo.Test;
SELECT DATALENGTH(c), DATALENGTH(nc), DATALENGTH(vc), DATALENGTH(nvc)
FROM dbo.Test;
go
DROP TABLE dbo.Test;
go
Run this twice, with the ANSI_PADDING option ON and OFF. As you see, the DATALENGTH of the vc column is affected by the option. But not because the function behaviour is changed by the option - the change is in the INSERT statement. Here, the trailing spaces will be removed from the varchar column when the option is off. Since the trailing spaces are removed, only the letters abc are stored, and DATALENGTH correctly reports three bytes used.
Some important warnings:
1. ANSI_PADDING affects varchar columns, but not nvarchar columns. And varchar(max) columns are also excluded.
2. ANSI_PADDING only affects data that is stored in a table. When assigning values to a variable, this option has no effect.
3. ANSI_PADDING is a deprecated option. Both the option and the ANSI_PADDING OFF behaviour will be removed in a future version of SQL Server. Don't use this option in new code, and replace it if it's still being used in existing code.

wow... superb explanation... thanks dinesh, hugo.....cool....;-)

Manik
You cannot get to the top by sitting on your bottom.

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 14

Thanks for your detailed explaination Hugo. I can find definitions for each and every function seperately in BOL. But i can't find such relative explainations over there.

--
Dineshbabu
Desire to learn new things..