Easy SQL: Moving Values

You have access to a table of monsters as follows:

** monsters table schema **

• id
• name
• legs
• arms
• characteristics

Your task is to make a new table where each column should contain the length of the string in the column to its right (last column should contain length of string in the first column). Remember some column values are not currently strings. Column order and titles should remain unchanged:

** output table schema **

• id
• name
• legs
• arms
• characteristics

 

Solution:

SELECT 
  LENGTH(name) AS id, 
  LENGTH(CAST(legs as VARCHAR)) AS name, 
  LENGTH(CAST(arms as VARCHAR)) AS legs, 
  LENGTH(characteristics) AS arms, 
  LENGTH(CAST(id as VARCHAR)) AS characteristics 
FROM 
  monsters

You can change the type more simply by using the '::' symbol instead of CAST().

 

 

 

SELECT 
  LENGTH(name) AS id,
  LENGTH(legs::text) AS name,
  LENGTH(arms::text) AS legs,
  LENGTH(characteristics) AS arms,
  LENGTH(id::text) AS characteristics
FROM
  monsters

 

 

Result:

id	name	legs	arms	characteristics
5	4	5	11	1
4	4	3	19	1
5	5	5	9	1
4	4	4	14	1
6	4	3	23	1