How to count the dot in a string

dataservices job design
1

Hi All,

I have specific requirement like the column values can be as follows.

Ex: 10.20.001.25
10.20.001
20.12.35
20.12

Now the requirement I need to count the dot(.) in 10.20.001.25 and I will have to reduce one dot(.) from this string and find if it is existing ort. If it is not existing, then reduce by one more dot and compare in the existing set again.

So If pass 10.20.001.25 to the custom function then it should return 10.20.001

If I pass 20.12, then it is not in the existing data set even though after reduced by one dot (.) so it should return NULL.

Any thoughts ?

Appreciate your inputs.

bodideveloper (BOB member since 2009-08-29)

2

You could try something like this:-

$L_Last_Value = word_ext(<>, -1, ‘.’);
$L_Length_of_Last_Value = length($L_Last_Value) + 1 ;
$L_Length_of_Your_String = length(<>);

$L_Lookup_Value = substr(<>, 1, ($L_Length_of_Your_String - $L_Length_of_Last_Value);

Which will give you the value to lookup against the orginal data set, using the ‘lookup_ext’ function.

I’ve not had time to try this so you will probably need to tweak the syntax.

Let us know how you get on.

Nemesis :australia: (BOB member since 2004-06-09)

3

this is one of those basic string functions that I believe should be included out of box…

$curLoc 	= index($inputString,$searchChar,1);
$counter 	= 0;
while ($curLoc > 0)
	begin
		$counter 	= $counter + 1;
		$curLoc 	= index($inputString,$searchChar,$curLoc + 1);
	end
return($counter);

jlynn73 :us: (BOB member since 2009-10-27)

4

I agree. When I started to look at this, I assumed that it wouldn’t be that hard and one of the existing functions could be used :?

Nemesis :australia: (BOB member since 2004-06-09)

5

Thank you all for responding. Actually I resolved it.

$Count = length($inputCode ) - length(replace_substr($inputCode , ‘.’, ‘’));

$SearchStr = NULL;

$Status = 0;

$outputCode = -1;

If ($Count > 0)
begin
$Cnt2 = $Count;

  While ($Cnt2 > 0)
  begin

	 If ($Cnt2 = $Count)
       $SearchStr = word_ext($inputCode,$Cnt2,'.');
	 Else      
     $SearchStr = word_ext($inputCode,$Cnt2,'.')||'.'||$SearchStr;

	 $Cnt2  = $Cnt2  -1;

  end

   $outputCode = $SearchStr;

end

bodideveloper (BOB member since 2009-08-29)

6

Do the following:

  1. Take the length of the whole string with dots and store it in a new field say length_with
  2. Take the length of the whole string without dots and store it in a new field say length_without (It can be achived by using replace_substring func. Use the following code: replace_substring(field,’.’,’’)

Now if you do (length_without- length_with), will give you the count of the dots.

bodi567 (BOB member since 2010-12-15)

7

I like this. For the function you would then divide the difference by the length of the search string.

easier than looping :crazy_face: and maintaining word indexes :crazy_face:

jlynn73 :us: (BOB member since 2009-10-27)