SQL: How can I make a query to get the STRING that do not contain 3 letters 'r'?

Question

Example:

wordrr -> Incorrect because it have 3 'r'

word --> Correct becouse it not have 3 'r'

If that `N` might be variable, even something like this could work:`WHERE char_length(s)-char_length(replace(s, 'r', '')) < N` might be safer (re: length .vs. char_length). — Jon Armstrong, Oct 04 '21 at 18:47
@JonArmstrong that works for r, but for non-ascii characters, use char_length, not length, since length is length in bytes. example: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8a2f5ea32469bb130ffa37de877f373c — ysth, Oct 04 '21 at 18:56
Already asked&answered here: https://stackoverflow.com/questions/12344795/count-the-number-of-occurrences-of-a-string-in-a-varchar-field — Georg Richter, Oct 05 '21 at 05:20

score 2 · Accepted Answer · edited Oct 06 '21 at 21:39

You want to fetch those rows in which a specified column doesn't contain the character "r" 3 times.

Here is the code to count the occurrence of character "r".

SELECT (LENGTH(ColName) - LENGTH(REPLACE(ColName, 'r', '')))  as "R Count" from TableName;

Note: If LENGTH doesn't work, try using LEN.

Now in order to fetch rows which doesn't have character "r" occurring 3 times or more

SELECT * from TableName where (LENGTH(ColName) - LENGTH(REPLACE(ColName, 'r', '')))  < 3 ;

Also you can use:

SELECT * from TableName where (char_length(ColName) - char_length (REPLACE (ColName, 'r', '')))  <= 3 ;

1 Answers1