Subscribe to this thread
Home - General / All posts - SQL Count

1,084 post(s)
#19-Jan-23 16:22

Is it possible in MFD 8 SQL to count an letter like "/" in a column ?

I have a table with a lot of merged classes seperate with a "/", like here:

Alpenbock/Gelbbauchunke/Hohltaube/Mittelspecht/Rotmilan/Spanische Fahne

now i want to count the "/" so i know how much classes are in the column.

In my case = 6 ((5x"/" (+1))

Thanks for advice


3,396 post(s)
#19-Jan-23 17:18

you need to play around with the replace and length function:

VALUES (len("Alpenbock/Gelbbauchunke/Hohltaube/Mittelspecht/Rotmilan/Spanische Fahne") - len(replace("Alpenbock/Gelbbauchunke/Hohltaube/Mittelspecht/Rotmilan/Spanische Fahne","/","")))

now, I'm not using a column, I just force-fed it the string. But, here you see we are getting the length of the original string, and subtracting the new string when we replace all occurrences of "/" with "". It's just a little sleight-of-hand.

Again, Manifold 8 for the win!!! :-)


1,084 post(s)
#19-Jan-23 18:25

thank you Art, i will give it a try


676 post(s)
#19-Jan-23 18:56

Try this:



  , Len(RegExp([name]"[^/]*""")) + 1 AS numItems



I have added +1 to count the number of elements instead of the number of slashes. It removes every character that is not a / the counts the length of the remaining string.



1,084 post(s)
#20-Jan-23 06:55

That works perfectly Thank you !!!


10,447 post(s)
#25-Jan-23 12:55

For completeness, a similar SQL call in 9:


? StringRegexpCount(

   'Alpenbock/Gelbbauchunke/Hohltaube/Mittelspecht/Rotmilan/Spanische Fahne',


-- result: 5

A little more direct than in 8.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.