CODEWITHSUNDEEP
sql
Mark
Mark

Reputation: 1

Counting for specific characters in a row - SQL

I'm looking to see if it's possible to only display results with multiple instances of a single character. For instance, if I wanted to only show rows that have the "," character in it more than 6 times.

Example: I, am, trying, to, figure, out, this, query

Would show in the results, but any row with less than 6 ","'s wouldn't show.

Upvotes: 0

Views: 336

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

Here's a way using length and replace to show strings that have , 6 or more times:

select * from mytable where length(mycolumn) - length(replace(mycolumn, ",", "")) >= 6

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method uses like:

select t.*
from t
where col like '%,%,%,%,%,%,%';

This should work in any database.

Upvotes: 0

Related Questions