Menu
Does anyone know a good way to count characters in a text (nvarchar) column in Sql Server? The values there can be text, symbols and/or numbers.
So far I used sum(datalength(column))/2 but this only works for text. (it's a method based on datalength and this can vary from a type to another).
625
LEN() function calculates the number of characters of an input string, excluding the trailing spaces. It is an expression that can be a constant, variable, or column of either character or binary data. Returns : It returns the number of characters of an input string, excluding the trailing spaces.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
SQL Server LEN() Function The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.
nvarchar [ ( n | max ) ]n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
You can find the number of characters using system function LEN. i.e.
SELECT LEN(Column) FROM TABLE Use
SELECT length(yourfield) FROM table; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
