Postgresql: check if letter repeats in string

Question

I have a table:

id   |  string
1    |  a,b,c,d,a,b
2    |  a,a,c,d,a,b
3    |  a,b,c,d

I want to check if letter 'a' repeats in string using regular expressions. How should i do that?

I try with

select * from table where string ~ 'a[^a]*'

but its not working.

Expected result:

id   |  string         | repeat_a
1    |  a,b,c,d,a,b    | t
2    |  a,a,c,d,a,b    | t
3    |  a,b,c,d        | f

What you want technically, is the '(.*a){2}' pattern, but you should think of a better db normalization, like a joining table instead of multiple values (separated by ,) in the string column — pozs
– pozs, Commented May 11, 2016 at 12:36
+1 to what @pozs said. Normalize this design if possible, having non-sargable columns will cause inordinate amounts of pain. Plus, you can't set useful indexes on such a column. — s.m.
– s.m., Commented May 11, 2016 at 12:41

Tim Biegeleisen · Accepted Answer · 2016-05-11 12:36:12Z

2

One approach is to compare the input string against that string with all occurrences of the letter 'a' removed. A record which matches would then be characterized by the length of the replaced string being 2 or more characters shorter than the original input.

SELECT * FROM table
WHERE char_length(string) > char_length(replace(string, 'a', '')) + 1

answered May 11, 2016 at 12:36

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ezequiel Tolnay · Accepted Answer · 2016-05-12 02:54:21Z

1

If your letter a is a value in a comma-separated list of values, then scanning for occurrences of the letter a may not be sufficient, as the letter may be contained in a value and not be "the" value. If that would be the case, you could do

SELECT string, val, count(*)
FROM table
JOIN LATERAL string_to_array(string, ',') val ON TRUE
WHERE val = 'a' -- remove this to see all the repeating values
GROUP BY val, string
HAVING COUNT(*) > 1;

answered May 12, 2016 at 2:54

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Collectives™ on Stack Overflow

Postgresql: check if letter repeats in string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related