Clever use of SUBSTRING on Codewars, can someone plesae explain to me how this works? - Postgres

user_7125982 0
Viewed 59

Please assist me in explaining how the coder created this great solution, I'll briefly explain the table:

select * from people

returns the value:

name
Anibal Dorothea Tromp IV

The challenge is solved in many ways, but one solution really amazed me:

select
  substring(name, '^(.+)\s\S+\s\S+$') as name,
  substring(name, '^.+\s(\S+)\s\S+$') as first_lastname,
  substring(name, '^.+\s(\S+)$') as second_lastname
from people

returns the result:

name             first_lastname   second_lastname  
Anibal Dorothea  Tromp              IV

the real table has many more rows, some with 1 first name, others with 2 first names. So the challenge was to separate the names with the first name (name) being a different length word and sometimes more than 1 word.

If I look at the documentation on substring, there is no mention of this method, please explain to me how his great solution works or point me in the direction of a site that explains some of the components of this solution.

Many thanks, Tim

substring postgresql sql
2 Answers

This special string you are referring to is called a regular expression. Regular expressions are available in many RDBMS and programming languages.

Postgres does have documentation on this here: https://www.postgresql.org/docs/current/functions-matching.html

The query above, has three substring with regex patterns in them:

  1. '^(.+)\s\S+\s\S+$' matches every group except the last two. Anibal Dorothea
  2. '^.+\s(\S+)\s\S+$' matches the second last group Tromp
  3. '^.+\s(\S+)$' mathces the final group IV

If you changed the input text to Anibal Dorothea Tromp the IV the output from the select above would be:

name                    first_lastname   second_lastname  
Anibal Dorothea Tromp              the                IV
user_5501980

Although you can use substring() like this -- and it is well documented -- this seems like a good use case for split_part():

select split_part(name, ' ', 1),
       split_part(name, ' ', 2),
       split_part(name, ' ', 3),
       split_part(name, ' ', 4)

Or if you need a more advanced splitter, you can use regexp_split_to_array().

user_11440350
Related