Fetching second word from a string in SQL Oracle

Question

I need to get the second word of each name in a column, I did my research about substr and instr, but I think I have a logical error, I simply cannot point it out.

select substr(vendor_name,instr(vendor_name, ' ')+1) from vendors ORDER BY vendor_id asc;

Here is my current output, the code works, but it does not do what I want it to do.

Answer 1

Try this query:

SELECT SUBSTR(vendor_name,
              INSTR(vendor_name, ' ', 1, 1) + 1,
              INSTR(vendor_name, ' ', 1, 2) - INSTR(vendor_name, ' ', 1, 1) - 1)
FROM vendors
ORDER BY vendor_id

To see how this works, take an example vendor_name of Boeing Corporation Inc. In this case:

INSTR(vendor_name, ' ', 1, 1) = 7  = first occurrence of space
INSTR(vendor_name, ' ', 1, 2) = 19 = second occurrence of space

Now here is the call we are making to substring:

SELECT SUBSTR('Boeing Corporation Inc.',
              7 + 1,
              19 - 7 - 1)

which is the same as

SELECT SUBSTR('Boeing Corporation Inc.', 8, 11) = 'Corporation'

Note that my answer assumes that there is a second space present in the vendor_name (and also a first space). If you expect complex data in this column, you might want to use a regular expression instead.