Home Dashboard Directory Help
Search

Request for string len() function that includes count of trailing spaces by crokusek3


Status: 

Closed


3
0
Sign in
to vote
Type: Suggestion
ID: 801381
Opened: 9/18/2013 12:19:04 PM
Access Restriction: Public
0
Workaround(s)
view

Description

Hi,

For this example:

declare @s nvarchar(30) = '1234    ';
select len(@s)

The len() returns 4 but a need exists to account for spaces and return 8.

Please review attempted workarounds:

http://stackoverflow.com/questions/2025585


Using datalength() / 2 in place of len() is not consistent because it is collation dependent. Example here:

http://stackoverflow.com/questions/176514/what-is-the-difference-between-char-nchar-varchar-and-nvarchar-in-sql-server/8250586#8250586


Thanks,
Chris
Details
Sign in to post a comment.
Posted by crokusek3 on 9/20/2013 at 11:53 AM
As stated in the original request, using datalength() / 2 in place of len() is not consistent because it is collation dependent. This does not appear to be a valid workaround.


DECLARE @T TABLE
(
C1 VARCHAR(20) COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS,
C2 NVARCHAR(20)COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS
)

INSERT INTO @T VALUES (N'中华人民共和国',N'中华人民共和国')

SELECT LEN(C1)        AS [LEN(C1)],
     DATALENGTH(C1) AS [DATALENGTH(C1)],
     LEN(C2)        AS [LEN(C2)],
     DATALENGTH(C2) AS [DATALENGTH(C2)]
FROM @T
Returns

LEN(C1)     DATALENGTH(C1) LEN(C2)     DATALENGTH(C2)
----------- -------------- ----------- --------------
7         12             7         14


Above copied from link:

http://stackoverflow.com/questions/176514/what-is-the-difference-between-char-nchar-varchar-and-nvarchar-in-sql-server/8250586#8250586
Posted by Microsoft on 9/20/2013 at 10:51 AM
Thanks for your response and for the additional clarification.

There is an existing function called datalength() that does not remove trailing spaces. Please refer to http://technet.microsoft.com/en-us/library/ms173486.aspx for further details.

Thanks again for your continued support in our products.
Posted by crokusek3 on 9/19/2013 at 7:07 PM
I am not requesting a change the existing len(), but rather the adding of a new function. Would that change your answer?

By the way, could you provide an order of magnitude estimate of how many votes it would approximately take to get something as trivial as this to be considered? 100, 1000?
Posted by Microsoft on 9/19/2013 at 5:41 PM
Thank you for submitting this feedback.

The function len() does not include trailing spaces, and this is by design.

Thanks for your ocntinued support in our products.
Sign in to post a workaround.