45

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

Sam
  • 7,252
  • 16
  • 46
  • 65
Sam
  • 3,067
  • 19
  • 53
  • 55
  • 2
    `TEXT` is deprecated and shouldn't be used. – Oded Jan 10 '13 at 10:08
  • 1
    "but this only works for text" how so? Everything that goes in an `nvarchar` column has to be, at the end of the day, composed of sequences of unicode characters, stored as UTF-16, and your method counts how many of those characters there are. – Damien_The_Unbeliever Jan 10 '13 at 10:13

6 Answers6

82

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

SELECT LEN(Column) FROM TABLE
TechDo
  • 18,398
  • 3
  • 51
  • 64
  • 1
    'LEN' will give you the count till 4000 only. So, how you will get the count of characters greater than 4000 in length? – SHEKHAR SHETE Nov 12 '14 at 05:06
  • 1
    I didn't get you... I guess it counts string value more than 4000 length. Please confirm. – TechDo Nov 12 '14 at 06:44
  • 1
    hi @TechDo, I have an HTML content and using your solution i have tried to get length of the content which is more than 4000 charcters but dont know why it returns lenght as 4000 only!So, i said..!any idea about this? – SHEKHAR SHETE Nov 12 '14 at 08:48
  • 1
    @SHEKHARSHETE There is no character limit: http://msdn.microsoft.com/en-us/library/ms190329%28v=sql.120%29.aspx – Dan Dec 09 '14 at 19:57
  • Yes! i agree! but by SQLserver allow me not more than 4000 charcters! do we need to do any setting in Management studio? – SHEKHAR SHETE Dec 11 '14 at 04:23
  • I tried in MySQL as well, and it says `#1305 - FUNCTION sampleDB.len does not exist` when I did so. And using `length` instead of `len` worked. – Rajith Gun Hewage May 28 '16 at 18:54
  • FYI, in postgres, this is `SELECT LENGTH(column) FROM table`. – bwest87 Sep 01 '16 at 21:49
  • would this also consider whitespace if it was included in the field? – Jonnie Jul 30 '20 at 07:10
13

Use

SELECT length(yourfield) FROM table;
Marc
  • 6,154
  • 1
  • 15
  • 10
7

Use the LEN function:

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

Oded
  • 489,969
  • 99
  • 883
  • 1,009
5

Doesn't SELECT LEN(column_name) work?

Liam
  • 27,717
  • 28
  • 128
  • 190
Sandip Bantawa
  • 2,822
  • 4
  • 31
  • 47
1

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

Liam
  • 27,717
  • 28
  • 128
  • 190
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
0

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.

ORSJess
  • 1