Word_ext function...

dataservices job design
1

Hi all,

with version 11.5, I want to use word_ext function to split a comma separated string containing 4 elements :

‘A,B,C,D’ =>

  1. ‘A’
  2. ‘B’
  3. ‘C’
  4. ‘D’

The problem is when there are empty elements in the string like ‘A,C,D’. In that case, I would like :

  1. ‘A’
  2. null
  3. ‘C’
  4. ‘D’

Word_ext skips consecutive delimiters so I can’t use it. Is there another function what would do the trick ?

One solution would be to first replace ‘,’ by ‘, ,’ in the incoming string then use word_ext. But if there is another way, please advise !

Thanks in advance.

ombo (BOB member since 2005-11-16)

2

There are other ways, but none are as simple. I would use:

word_ext(replace_substr($CSV, ‘,’, ‘, ,’), 1, ‘,’)

The alternative would be a combination of substr, index and either decode or replace_substr that is not nearly as compact and readable. The above also gracefully handles having no final word, which would require more custom code the other way.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

3

The only issue with the above code is that it will not capture more than two consecutive delimiters. Take for example A,B,C - the result will not be:

A
B
NULL
NULL
C

as expected.

I’m currently writing a custom and messy function to deal with this as it’s in one of our requirements…

adelauw (BOB member since 2007-10-22)

4

A) Congratulations on resurrecting an 8+ year old thread! =)
B) That was not part of the original request.
C) Custom function is probably the best route. Outside of that you could probably use regex_replace, which did not exist when this question was posted.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

5

Here ya go. Theres no logical reason why you would want to trim delimiters in this function. :crazy_face:

$turnString = $inputString;

$curIdx 	= 1;
$retWord 	= '';
while($curIdx <= $wordNumber and $retWord = '')
	begin
		if($curIdx = $wordNumber)
			begin
				if(substr($turnString,1,length($delimiter)) = $delimiter)
					begin
						$retWord = null;
					end
				else 
					begin
						if(index( $turnString ,$delimiter,1) > 0)
							begin
								$retWord = substr( $turnString,1,index( $turnString ,$delimiter,1) - 1);
							end
						else 
							begin
								$retWord = $turnString;
							end
					end
			end
		else 
			begin
				$turnString = substr($turnString,index( $turnString,$delimiter,1) + length($delimiter),10000);
				$curIdx = $curIdx + 1;
			end
	end

return($retWord);

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