Sample from the database (rows)

Question

PostgreSQL

How to select from a table all strings:
1) consisting of more than 7 words
2) containing only characters from 'a' to 'z', from 'a' to 'z', numbers and the sign '-'

Answer 1

1. containing 6 or more word separators
2. possibly regular expressions

Answer 2

postgresmen.ru/articles/view/33
and further to the original documentation
www.postgresql.org/docs/8.3/static/functions-matching.html

Answer 3

1) SELECT * FROM "public".words WHERE key_word !~* '[0-9a-za...i]\-\r\n\20' LIMIT 10
Finds everything, including the necessary and unnecessary.
2) Search for 6 delimiters (spaces): SELECT * FROM "public".words WHERE key_word ~* '\20{6,}' LIMIT 10
Doesn't work at all (looks for consecutive spaces?)

Answer 4

Or hardcode: (any_characters)(separators)(any_characters)(separators)(any_characters)(separators)(any_characters)(separators)(any_characters)(separators)(any_characters) as a large regular expression. Or something trickier, like: ((any_characters)(separators)){6}

Answer 5

1) consisting of more than 7 words

No regexps:
If separators are spaces, number of words = length(str)-length(replace(str,' ','')) + 1
You can remove duplicate spaces like this: replace(replace(replace(str,' ', '~'),'~',''),'~','')
select *
from (select str,replace(replace(replace(str,' ','~ '),' ~',''),'~','') sstr from t) s
where length(sstr)-length(replace(sstr,' ','')) +1 > 7
Регекспами:
select * from t where ' '||str ~ '(\s+\S+){8,}'

2) содержащих только символы от 'a' до 'z', от 'а' до 'я', цифры и знак '-'

select * from t where str ~ '^[-\w\dа-я]+$'