I have one column ip(which is string) which looks like this

a.b.c.d

I would like to substrate the first 3 elements(after being split by '.') of this ip and the result should be:

a.b.c

I tried to use the method in this post:Split and return all but first element in Presto, but found out that position('.' in ip) only returns the first element that meets the condition.

CodePudding user response：

• Split the ip into an array with split
• Take the last item off the array with trim_array
• Put it back together again with array_join

array_join(trim_array(split(col, '.'), 1), '.')

See Array Functions and Operators and String Functions

Using pure string functions, we can use your linked answer by reversing everything and then un-reversing:

reverse(substr(reverse(col), position('.' in reverse(col))   1))