I have for example the following column:

I want to get the following:

Thanks !

CodePudding user response：

You can use below code.

select split('A B C D E',' ')[ length('A B C D E')- length(replace('A B C D E',' ','') )-1 ] col 

split - this is going to choose particular string based on spaces.
length - This is used intelligently to calculate last string before last space.
length (whole string ) - length (whole string without spaces) - This should give you number of spaces. Do a -1 to get last but one space count.

CodePudding user response：

You can use regexp_extract:

with mytable as ( select 'A B C D E' col1 union all select 'A B C E' union all select 'A B C' )

select regexp_extract(col1, '\s ([^\s] )\s [^\s]*$',1) from mytable

Result:

D
    
C
    
B

Regex '\\s ([^\\s] )\\s [^\\s]*$' means:

\\s - space 1 times

([^\\s] ) - group to extract, not space 1 times

\\s - 1 space

[^\\s]* - not a space any times

enter code here$ - end of the string