0

I am trying to count the number of characters in a string, but every value I return is '100'. The values are discussion question responses in Blackboard. Here is the query I am using:

SELECT char_length(dse.event_text)
FROM discussion_stream_event dse

This returns a '100' for every line even though the responses can range from a dozen characters to more than a thousand. The data type for dse.event_text is character varying, but even when I cast it as text the value returned is still 100.

Any ideas how I might count these characters? Thank you!

Matthew Fillo
  • 25
  • 5
  • Unless you have some whitespace that pads these things to 100 characters (and that whitespace isn't a trailing space character which a varchar would automatically ignore upon insertion) then I can't imagine what the issue would be. Do you get different results when using function `length()`? – JNevill Mar 16 '22 at 15:49
  • same result actually – Matthew Fillo Mar 16 '22 at 16:06
  • 1
    Sounds like your column event_text is defined al `char(100)`; Try running: `SELECT char_length(trim(dse.event_text)) FROM discussion_stream_event dse;` – Belayer Mar 16 '22 at 16:17
  • @JNevill, that is not the case: `select length('oooo '::varchar); 10`. – Adrian Klaver Mar 16 '22 at 16:21
  • What is the exact definition of the column type? Have you recently upgraded the database or the OS it runs on? – Adrian Klaver Mar 16 '22 at 16:24
  • @AdrianKlaver Column name = event_text Data type = nvarchar(333) Identity? = false Nullable? = true Description = Text specific to this event - not the stream text directly, but the dynamic text to substitute into an event-appropriate message when rendering the stream. – Matthew Fillo Mar 16 '22 at 16:26
  • @AdrianKlaver You are right. Strike my comment on right-padded spaces in varchar(). That is a likelyhood in this scenario. – JNevill Mar 16 '22 at 16:27
  • 1
    `nvarchar` is not a Postgres type, are you sure you are looking at the correct database? – Adrian Klaver Mar 16 '22 at 16:31
  • 1
    After reading my last post I realized the table I am using contains truncated text content. I can connect to another table and pull the full text from there. Thank you everyone! – Matthew Fillo Mar 16 '22 at 16:32

0 Answers0