1

I am interested in identifying records where a string is made up of 1 repeating character such as 'AAA', 'ZZZZZ', 'EE' But not 'A', 'HELLO', 'APPLE', 'AABB', 'MM ALBERT'

The following regex works when I test it ^(.)\1{1,}$ https://regex101.com/r/dT6dK8/46 But it does not work when I am using it in Snowflake. Any advice would be appreciated.

What I am testing in Snowflake

SELECT 'AA' regexp '^(.)\1{1,}$' -- AS 'EXPECT TRUE'
       ,'AAA' regexp '^(.)\1{1,}$' -- AS 'EXPECT TRUE'
       ,'A' regexp '^(.)\1{1,}$' -- AS 'EXPECT FALSE'
       ,'AAAAA' regexp '^(.)\1{1,}$' -- AS 'EXPECT TRUE'
       ,'BBBB' regexp '^(.)\1{1,}$' -- AS 'EXPECT TRUE'
       ,'AABB' regexp '^(.)\1{1,}$' -- AS 'EXPECT FALSE'
       ,'HELLO' regexp '^(.)\1{1,}$' -- AS 'EXPECT FALSE'       
       ,'AAAAA' regexp '^(.)\1{1,}$' -- AS 'EXPECT TRUE'
       ,'BB BB' regexp '^(.)\1{1,}$' -- AS 'EXPECT FALSE'
;

References Regex to determine if string is a single repeating character

jcsql
  • 75
  • 6

1 Answers1

1

Without using regexp:

SELECT col, REPLACE(col, LEFT(col,1), '') = '' AND LENGTH(col) > 1
FROM (SELECT 'AA' AS col UNION ALL SELECT 'HELLO') t;

db<>fiddle demo

If replacement by using first character with empty string returns empty string it means that all were the same.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    Thats clever solution. If the string is made up of all the same characters then replacing the first char with all '' would make it an empty string. However that does not solve the case to allow for Single Char Strings. where 'A' is a valid entry – jcsql Jul 30 '20 at 18:24
  • 1
    This doesn’t work for a string made of a single character. You might need to add a condition on the length of the string. – GMB Jul 30 '20 at 18:27
  • 1
    `IF(LEN(col) = 1, col, REPLACE(col, LEFT(col,1), ''))` would fix 1-letter issue. – Aleksei Matiushkin Jul 30 '20 at 18:30
  • Thanks for nitpicking and both ideas :) – Lukasz Szozda Jul 30 '20 at 18:30
  • By adding an AND statement with it easily solves the Single Character problem. I appreciate the help. I am still curious though, why it doesn't work for Snowflake but in the regex tester – jcsql Jul 30 '20 at 18:36