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

Example:

wordrr -> Incorrect because it have 3 ‘r’

word –> Correct becouse it not have 3 ‘r’

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 ;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating