How to get number of chars in string in Transact SQL, the "other way"

Question

Hi, we faced very strange issue (really strange for such mature product): how to get number of characters in unicode string using Transact-SQL statements. The key problem of this issue that the len() TSQL function returns number of chars, excluding trailing blanks. the other variant is to use datalength (which return number of bytes) and divide by 2, so get numbers of unicode chars. But Unicode chars can be surrogate pairs so its wont work either.

We have 2 variants of solution, first is use len(replace()) and second is add single symbol and then substract 1 from result. But imo both variants are rather ugly.

declare @txt nvarchar(10)
set @txt = 'stack ' 
select @txt as variable, 
 len(@txt) as lenBehaviour, 
 DATALENGTH(@txt)/2 as datalengthBehaviour, 
 len(replace(@txt,' ','O')) as ReplaceBehaviour, 
 len(@txt+'.')-1 as addAndMinusBehaviour

Any other ideas how to count chars in string with traililng spaces ?

Answer 1

I can't leave a comment so I will have to leave an answer (or shutup).

My vote would be for the addAndMinusBehaviour

I haven't got a good third alternative, there maybe some obscure whitespace rules to fiddle with in the options / SET / Collation assignment but don't know more detail off the top of my head.

but really addAndMinusBehaviour is probably the eaiest to implement, fastest to execute and if you document it, farily maintainable as well.

Answer 2

My understanding is that DATALENGTH(@txt)/2 should always give you the number of characters. SQL Server stores Unicode characters in UCS-2 which does not support surrogate pairs.

http://msdn.microsoft.com/en-us/library/ms186939.aspx

http://en.wikipedia.org/wiki/UCS2