Regular Expressions in Oracle

Question

'Select from CITIES tables only records with composite names separated by a single space'

TABLE CIDADES

ID  CIDADE
01  SAO PAULO
02  RIO DE JANEIRO
03  SAO CARLOS
04  TAUBATÉ
05  SÃO JOSÉ DO RIO PRETO

The Query can return only

TABLE CIDADES

ID  CIDADE
01  SAO PAULO
03  SAO CARLOS

HOW TO WRITE A REGULAR EXPRESSION THAT RETURNS TO ME CITIES COMPOSED OF ONLY 2 NAMES?

Answer 1

I’m going to assume we do this check for the number of spaces...

 select * from cidades 
 where regexp_count(cidade,' ')=1

Answer 2

For cases where there is more than one space, one option is this:

SELECT * FROM CIDADES 
WHERE REGEXP_COUNT(CIDADE, '[[:alpha:]]\s{1,}[[:alpha:]]') =  1;