CODEWITHSUNDEEP
sqlsql-server-2008datalength
Sam
Sam

Reputation: 3167

count number of characters in nvarchar column

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).

Upvotes: 49

Views: 291321

Answers (6)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36621

text doesn't work with len function.

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.

Source

Upvotes: 1

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

Doesn't SELECT LEN(column_name) work?

Upvotes: 5

Marc
Marc

Reputation: 7894

Use

SELECT length(yourfield) FROM table;

Upvotes: 14

ORSJess
ORSJess

Reputation: 1

I had a similar problem recently, and here's what I did:

    SELECT
         columnname as 'Original_Value',
         LEN(LTRIM(columnname)) as 'Orig_Val_Char_Count',
         N'['+columnname+']' as 'UnicodeStr_Value',
         LEN(N'['+columnname+']')-2 as 'True_Char_Count'
    FROM mytable

The first two columns look at the original value and count the characters (minus leading/trailing spaces).

I needed to compare that with the true count of characters, which is why I used the second LEN function. It sets the column value to a string, forces that string to Unicode, and then counts the characters.

By using the brackets, you ensure that any leading or trailing spaces are also counted as characters; of course, you don't want to count the brackets themselves, so you subtract 2 at the end.

Upvotes: 0

Oded
Oded

Reputation: 499002

Use the LEN function:

Returns the number of characters of the specified string expression, excluding trailing blanks.

Upvotes: 7

TechDo
TechDo

Reputation: 18629

You can find the number of characters using system function LEN. i.e.

SELECT LEN(Column) FROM TABLE

Upvotes: 86

Related Questions