BusinessObjects Board

How do I select the middle word froma string?

building semantic layer
#1

Anyone know the SQL to use to select the middle word from a text column? Eg I need to select ‘Jane’ from ‘Mary Jane Smith’?

I am using the following to extract the first name I.e. ‘Mary’, but can’t work out the SQL to extract the middle name: usingsubstr(NAME,1, instr(NAME,’ '))

Thanks

#2

A colleague found the answer …
substr( NAME, instr(NAME,’ ‘,1,1), instr(,’ ‘,1,2)-instr(NAME,’ ',1,1))
From, First Occurrence of Space, Second Occurrence - First Occurrence of Space

2 Likes
#3

Thanks for coming back to post your solution.