We would like to do the following:
SELECT * FROM table WHERE char_length(text) >= 15 AND char_length(text) <= 100
HOWEVER, we want it to return only 'text' column that matches this query WITHOUT hashtags. that is:
If text = 'hello how are you' will match
but
text = 'hello #how #are you' will not match (because we want to filter out the hashtags and char_length('hello you') is too short).
You can remove hashtags from the text and check that the remaining string is within your desired length bounds:
with t as (select 'this is some text' as txt union all
select 'this is #hashed text - loong' union all
select 'too short #despite #many #hashtags')
select * from t
where length(regexp_replace(txt, '#[a-z]+ *', '', 'g')) between 15 and 100
You might want to fiddle a bit with the regexp #[a-z]+ * recognizing your tags. Note that the 'g' makes regexp_replace replace all occurrences of the regexp, not just the first one, see PostgreSQL docs.
# when it be not associated with a hashtag.Try using a POSIX regex which does not match the pattern .*([ ]|^)#[a-z].*:
SELECT *
FROM table
WHERE
char_length(text) >= 15 AND
char_length(text) <= 100 AND
text !~* '.*([ ]|^)#[a-z].*';
Where text !~* is already excluding the hashtags, what do you mean by "do char_length excluding the hashtags"
'hello how are you #yolo', which OP presumably wants to be included.
#could be in the middle of a string and not be part of a hashtag. Your suggestedLIKEexpression would fail all pound signs.