Select from a column argument with multiple arguments

Question

Good morning, I came across a question I can’t resolve.

I have the following code:

select id, name, characteristics as characteristic
from monsters
order by id

Return me the following table:

id  name    characteristic
1   Cyril   big, smelly
2   Tiny    small, stinky, loud
3   Niall   flatulent
4   Umph    idiotic, nasty
5   Martin  mad, stupid, bad-breath

I am being asked to return only the first word of the characteristic... How can I solve?

Thank you

Answer 1

You first need to locate the first occurrence of the comma, and select the characteristics up to the position where it lies. To select part of a string, you can use the subtring function, to find the first occurrence of a given character you can use the charindex function. Here is an example:

select id, name, substring(characteristics,1,charindex(',',characteristics,1)-1) as characteristic
from monsters
order by id

Another simpler way to get to the same result would be to use the LEFT function. Here’s an example with Mysql

select id, name, LEFT(characteristics,LOCATE(' ',characteristics) - 1) as characteristic
from monsters
order by id
SELECT