The below statement is yielding everything past the first word in vendor_name. I need it to yield ONLY the second word in vendor_name while using the substring. I also need it to return a blank field if NULL. Is this possible?
select substring_index(vendor_name, ' ',-2) as 'Second Word'
from vendors;
Edit: I am thinking it needs to be something along these lines but I cannot get it to work: substring_index(substring_index(vendor_name, ' ', -2)vendor_name, ' ', 1) as 'Second Word'
If you just want the second word, I'd get the first two words, then from that result, pick the last word. That handles cases where you might have four or more words.
mysql> select substring_index('one two three four', ' ', 2) as s;
+---------+
| s |
+---------+
| one two |
+---------+
mysql> select substring_index(substring_index('one two three four', ' ', 2), ' ', -1) as s;
+-----+
| s |
+-----+
| two |
+-----+
COALESCE( ...substring expression..., '')Try this out:
select
case
when instr(vendor_name,' ') = 0 then ' ' //This will show blank for null.
else substring_index(substring_index(vendor_name,' ',2),' ',-1)
end as second_name
from t;
It will show blank for null too.
Hope it helps!