Find rows only with single character-CodePudding

I've got table with a single column 'col' with strings in each row like this:

col
a 
abc
bcc
bb
ccc
de
cc

I need to find rows only with one letter (repeats included):

a
bb
ccc
cc

DB doesn't matter. But for example let's say it's SQLite. I've tried to use regexp, but can't find a way to specify search for same letter in each row, even it is repeated unlimited times.

Thanks!

CodePudding user response：

As the RDBMS is of no concern, this works for SQL Server:

select * 
from t
where Replace(col, Left(col,1),'') = '';

CodePudding user response：

ANSI/ISO SQL answer: TRIM col's first character from col. In nothing is left, they were all the same:

select * from tablename
where trim(substring(col from 1 for 1) from col) = ''
  and char_length(col) > 0; -- to skip zero length column values

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e2053fd5c5a331308647f18d7eafcdce

Works on Postgresql, Mimer SQL, YugabyteDB and MySQL/MariaDB. (And Firebird, except that empty string also is returned.)