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

1,078 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


http://www.thegisservicesector.de

artlembo


3,299 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!!! :-)

volker

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

thank you Art, i will give it a try


http://www.thegisservicesector.de

Sloots

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

Try this:

SELECT

  [name]

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

FROM

  [Drawing]

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.

Chris


http://www.mppng.nl/manifold/pointlabeler

volker

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

That works perfectly Thank you !!!


http://www.thegisservicesector.de

adamw


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

For completeness, a similar SQL call in 9:

--SQL9

? 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.