Get value in string after 4th space SQL-CodePudding

Related question so now i have a table Test with 3 columns id, value and term

TEST

id value Item 1 AB CD EF GH IJ KL 1 4 78 78 10 9

I will like a query to get the value in the Item column after the 4th space. In this case that will correspond to 'IJ' in the value column and in the "Item' column it will return '10'

This is what i tried

select substring(item(REGEXP_COUNT( SPLIT( TRIM(REGEXP_REPLACE(value, '[^[:digit:]]', ' ')), 'IJ')[0] , ' ')

from Test

CodePudding user response：

Use split_part()

select split_part('AB CD EF GH IJ KL', ' ',5);

This function split the string on the chosen character and allows you to choose which one you'd like to return as a string. In this case, the 5th part.