You can translate the question and the replies:

Returning a substring up to the first occurrence of a special character

I have a string value that contains data seperated by the symbol "/" muliple times. I need to return a new field with only the string values up to the first occurrence of "/" without including the symbol. sample_string = abc/defg/hi/jk expected_output = abc I have tried different ways but cannot get the optimalized result. SPLIT('/\s+\w+', sample_string) SPLIT('/^[^/]*/', sample_string) The above snippets work but returns the data as an array "_cf_split_text_text_array" but I cannot use downstream. I did attempt to cast the array to text data type but no luck. Any assistance would be greatly appericated. Alan
17-05-2023 05:51:40 -0400

1 Answer

Hi, The SPLIT function returns an array, to retrieve an element in the array, it must be identified with an URI expression which makes your case more complicated. Meanwhile, to make it simple I would use POSITION and SUBSTRING function on this case, use POSITION to find the first index of "/" and then use SUBSTRING to retrieve the string in front of it. The expression shows as follows: `SUBSTRING( VALUE , 0 , POSITION( '/' in VALUE) - 1 )` I have tested on my environment and this expression can successfully retrieve `abc` from `abc/defg/hi/jk`. Hope this helps!
Denodo Team
17-05-2023 22:42:11 -0400
You must sign in to add an answer. If you do not have an account, you can register here