How to extract all characters between last and second last spaces?

Question

I have for example the following column:

col1
A B C D E
A B C E
A B C

I want to get the following:

col1
D
C
B

Thanks !

Answer 1

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

Answer 2

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.