SQL Basics - Trimming the Field

You have access to a table of monsters as follows:

monsters schema

• id
• name
• legs
• arms
• characteristics

The monsters in the provided table have too many characteristics, they really only need one each. Your job is to trim the characteristics down so that each monster only has one. If there is only one already, provide that. If there are multiple, provide only the first one (don't leave any commas in there).

You must return a table with the format as follows:

output schema

• id
• name
• characteristic

Order by id

 

 

 

Solution:

SELECT 
  id, 
  name, 
  SPLIT_PART(characteristics, ',', 1) AS characteristic
FROM 
  monsters 
ORDER BY id

In postgresql, use the split_part function to split a string with a delimiter. 

Usage is split_part('original string', 'character to be cut', position).

 

 

Result:

id	name	characteristic
1	Cyril	big
2	Tiny	small
3	Niall	flatulent
4	Umph	idiotic
5	Martin	mad