October 28, 2014 at 10:51 pm
Comments posted to this topic are about the item Formatted output
October 29, 2014 at 1:26 am
Nice question, thanks.
A bit curious as why you use DATALENGTH instead of just LEN.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 29, 2014 at 1:36 am
Koen Verbeeck (10/29/2014)
Nice question, thanks.A bit curious as why you use DATALENGTH instead of just LEN.
With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.
DECLARE @L TABLE (Txt nvarchar(20));
INSERT @L(TxT) VALUES(N'ABC ');
SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L
DATALENGTH returns 4, LEN returns 3
October 29, 2014 at 1:58 am
Louis Hillebrand (10/29/2014)
Koen Verbeeck (10/29/2014)
Nice question, thanks.A bit curious as why you use DATALENGTH instead of just LEN.
With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.
DECLARE @L TABLE (Txt nvarchar(20));
INSERT @L(TxT) VALUES(N'ABC ');
SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L
DATALENGTH returns 4, LEN returns 3
OK, good to know. But with DATALENGTH you have to be extra careful which data types you are using.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 29, 2014 at 2:27 am
Extra careful, as always..
BTW. Koen, we're not far apart, I'm in Kortenberg.
October 29, 2014 at 2:30 am
Louis Hillebrand (10/29/2014)
BTW. Koen, we're not far apart, I'm in Kortenberg.
That is indeed quite close 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 29, 2014 at 2:47 am
Koen Verbeeck (10/29/2014)
Louis Hillebrand (10/29/2014)
Koen Verbeeck (10/29/2014)
Nice question, thanks.A bit curious as why you use DATALENGTH instead of just LEN.
With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.
DECLARE @L TABLE (Txt nvarchar(20));
INSERT @L(TxT) VALUES(N'ABC ');
SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L
DATALENGTH returns 4, LEN returns 3
OK, good to know. But with DATALENGTH you have to be extra careful which data types you are using.
I prefer STUFF
SELECT
stuff(Ms, 1, 1,'')
FROM
( SELECT
';' + DepartmentName AS [text()]
FROM
Department
FOR
XML PATH('')
) AS T ( Ms )
October 29, 2014 at 2:51 am
Carlo Romagnano (10/29/2014)
I prefer STUFF
SELECTstuff(Ms, 1, 1,'')
FROM
( SELECT
';' + DepartmentName AS [text()]
FROM
Department
FOR
XML PATH('')
) AS T ( Ms )
+1
It's the one I use as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 29, 2014 at 3:45 am
Louis Hillebrand (10/29/2014)
Koen Verbeeck (10/29/2014)
Nice question, thanks.A bit curious as why you use DATALENGTH instead of just LEN.
With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.
DECLARE @L TABLE (Txt nvarchar(20));
INSERT @L(TxT) VALUES(N'ABC ');
SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L
DATALENGTH returns 4, LEN returns 3
The reason why uses DATALENGTH is because the column is DEFINED as NVARCHAR() and DATALENGTH counts number of bytes, and also for that reason is why is divided by 2. (1 UNICODE Character = 2 bytes)
Using LEN does not require to divide by 2 as count number of characters as you well said, so LEN() - 1 = (DATALENGTH() / 2) - 1 on NVARCHAR() datatypes.
Nice question!
** I also prefer STUFF() and concatenate the separator at the beginning rather than the end.
October 29, 2014 at 5:29 am
This was removed by the editor as SPAM
October 29, 2014 at 5:41 am
Thanks for a good question. String concatenation and splitting is always a good topic.
October 29, 2014 at 7:37 am
Thanks for a good question, Anju.
October 29, 2014 at 8:28 am
SELECT
SUBSTRING(Ms, 1, DATALENGTH(Ms)/ 2 - 1)
FROM
( SELECT
DepartmentName + ';' AS [text()]
FROM
Department
FOR
XML PATH('')
) AS T ( Ms )
The Ms string is 30 characters, 'Finance;HR;Developers;Testers;' but DATALENGTH(Ms) is 60. I'm guessing that [text()] accounts for the difference, but can you explain how this difference occurs?
I get the usage of text() as described here http://stackoverflow.com/questions/9493732/difference-between-text-and-string but I don't understand how text() operates in this context.
October 29, 2014 at 8:51 am
Note that DepartmentName is Unicode which means that there are 2 bytes per character, and DATALENGTH(Ms) returns 60 because it returns the number of bytes, not the number of characters, while LEN(Ms) would return 30 because it returns the number of characters whether Ms is Unicode or not. (Also, LEN does not include trailing spaces in the character count.)
You can see this if you run the following query:
SELECT
DepartmentName
, LEN(DepartmentName)
, DATALENGTH(DepartmentName)
FROM Department
October 29, 2014 at 9:01 am
James_R_Alves (10/29/2014)
SELECT
SUBSTRING(Ms, 1, DATALENGTH(Ms)/ 2 - 1)
FROM
( SELECT
DepartmentName + ';' AS [text()]
FROM
Department
FOR
XML PATH('')
) AS T ( Ms )
The Ms string is 30 characters, 'Finance;HR;Developers;Testers;' but DATALENGTH(Ms) is 60. I'm guessing that [text()] accounts for the difference, but can you explain how this difference occurs?
I get the usage of text() as described here http://stackoverflow.com/questions/9493732/difference-between-text-and-string but I don't understand how text() operates in this context.
[text()] is a misleading alias in this case... Note that is wrapped with [square brackets]
just run
SELECT
DepartmentName + ';' AS [text()]
FROM
Department
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply