0

I need to count instances of a set of characters (aeıioöuü) in each row.

For example:

"linebreak" should return 4.
"indent" should return 2
"quote" should return 3

I can count one single character with this:

SELECT LENGTH(col) - LENGTH(REPLACE(col, 'a', ''))

I also found a way of counting a set of characters:

SELECT LENGTH(col) - LENGTH(REPLACE(REPLACE(REPLACE(col, 'a', ''), 'e', ''), 'ı', ''))...

However this query becomes confusing as it continues to grow. Is there a better method with wildcards or something else?

sevenkul
  • 966
  • 1
  • 8
  • 14
  • i think you should read this http://stackoverflow.com/questions/1580017/how-to-replace-multiple-characters-in-sql – WKordos Jan 21 '13 at 08:56
  • It is good to meet CLR UDF. I will use it in subsequent projects. I am using a simpler method for my actual problem for now. – sevenkul Jan 21 '13 at 09:52

1 Answers1

1

What I actually needed is the rows that has a specific number of a, e, ı, i, o, ö, u or ü; not the number of occurences. The following query returns the rows which have only one vowel character:

SELECT COUNT(*) AS cnt FROM [table]
WHERE col Like '%[aeıioöuü]%'
AND col NOT LIKE '%[aeıioöuü]%[aeıioöuü]%'

And this returns the rows which have only two vowel characters:

SELECT COUNT(*) AS cnt FROM [table]
WHERE col Like '%[aeıioöuü]%[aeıioöuü]%'
AND col NOT LIKE '%[aeıioöuü]%[aeıioöuü]%[aeıioöuü]%'

And so on...

sevenkul
  • 966
  • 1
  • 8
  • 14
  • When I was looking answer for actual problem, I found myself in methods for counting characters, and then replacing characters, and then CLR UDF, and then Regex... – sevenkul Jan 21 '13 at 09:48