How do I extract only the second word of a string in SQL?

Question

I'm trying to figure out how to just show the second word in the vendor name, but I keep getting an error with the following code:

SELECT SUBSTR(
SUBSTR(vendor_name,CHARINDEX(' ',vendor_name)+1,len(vendor_name)),1,CHARINDEX(' ',
SUBSTR(vendor_name,CHARINDEX(' ',vendor_name)+1,len(vendor_name)))
)
FROM vendors

Can someone help please

Answer 1

CHARINDEX is not an Oracle function. I think the closest match would be INSTR. You can also use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR( vendor_name, '^[^ ]* ([^ ]*)', 1, 1, 'i', 1 )
  FROM vendors

Here is a SQLFiddle.