I only need help with number 5. My answer so far is listed after the problem. Wr
ID: 3657216 • Letter: I
Question
I only need help with number 5. My answer so far is listed after the problem.
Write a SELECT statement that returns these columns from the Vendors table:
1.The vendor_name column
2.The vendor_name column in all capital letters
3.The vendor_phone column
4.The last four digits of each phone number
5.The second word in each vendor name if there is one; otherwise, blanks
6.The vendor_phone column with the parts of the number separated by dots as in 555.555.5555
My answer so far:
SELECT vendor_name,
UPPER(vendor_name),
vendor_phone,
SUBSTR(vendor_phone, 11, 4),
SUBSTR(vendor_name, INSTR(vendor_name, ' ', 1, 1) +1 ),
REPLACE(
REPLACE(
REPLACE(vendor_phone
, ')' , '.' )
, '(' , '' )
, '-' , '.' )
FROM vendors;
Thank you!
Explanation / Answer
Hey, my answer obviously just replaces yours between the , , so im not going to bother writing your entire query for just part 5. ,case when instr(vendor_name,' ')>0 then case when instr(vendor_name,' ',1,2)>0 then substring(vendor_name,instr(vendor_name,' ')+1,instr(vendor_name,' ',1,2)-instr(vendor_name,' ')-1) else substring(vendor_name,instr(vendor_name,' ')) end else null end, The 2nd case is testing if the vendor name is more than 2 words, as we only want to return the 2nd word. if the second location of ' ' is valid, then we return the substring starting from the first ' ' location and the length between the two ' '. If it is only two words return everything after the first ' ', and if its one word, then return null which will display as a blank. You might need to play with the length section (instr(vendor_name,' ',1,2)-instr(vendor_name,' ')-1) in terms of -1 or more...not 100% sure, if there is an issue it will most likely be there.