Count spaces before a string value SQL-CodePudding

so I have a table TEST with 2 columns - id and value. I would like a count of the number of spaces before a specific string value.

id         value
1          AB CD EFD RA

I would like a query to count the number of spaces in the value column to the left of "EFD". In this case it should be 2.

Thanks!

I tried:

select ID,
       VALUE,
       regexp_substr(Value, 'EFD') AS "SUBSTRING", 
       regexp_instr(Value, 'EFD') AS "POSITION"

I would like to get the position of EFD in this array as well. I should get 3.

CodePudding user response：

select id, value,
       regexp_substr(Value, 'EFD') AS substring, 
       regexp_count(substring(Value, 1, regexp_instr(Value, 'EFD')-1), ' ') AS spaces_before_EFD,
       regexp_instr(Value, 'EFD') AS position
from test;